Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sum matrix values (2003)

    Hi all,
    i want to sum some values in a matrix (see example) -> we have to cluster a lot of amounts to themes and units and
    i tried with sumproduct but it didnīt work. has anyone of You an idea to help me?

    stef
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum matrix values (2003)

    Try this in cell D20:
    <code>
    =SUMPRODUCT(($C$4:$C$15=D$19)*($D$3:$J$3=$C20)*$D$ 4:$J$15)
    </code>
    Fill down to D23, then fill right to column I. Note the use of absolute and relative references: in D$19 the column is variable while the row is fixed, and in $C20 the column is fixed while the row is variable. The other references are absolute.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum matrix values (2003)

    thanks a lot again, Hans, it worked in my example but when i tried to put it into my matrix, it didnt..
    (example add).
    stef
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sum matrix values (2003)

    I don't know why, but the cells in C3:M48 that do not have numbers in them are not blank but are considered text.

    Select C3:M48,
    edit goto - <special>
    Select "Constants" option button
    and uncheck all but "Text"
    <ok>
    <del>

    and they will cleared and the formula will work

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum matrix values (2003)

    ah, something i havenīt seen yet,
    steve, thank You,
    stefan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sum matrix values (2003)

    I don't complete understand how this happened. I don't see any of the "clues" that the cells are not blank. I discovered it because I presumed you had at least 1 cell that was not blank that appeared blank (to give you the error in the formula) and was surprised when all of them were text.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum matrix values (2003)

    This can happen when data are imported from another application.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum matrix values (2003)

    well, i only copied it from the original mastersheet, there was no other application.
    Only from excel to excel. But Steves solution works fine....
    stefan

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum matrix values (2003)

    Strange, but what matters is that it works now...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •