# Thread: sum matrix values (2003)

1. ## 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

2. ## 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. ## 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..
stef

4. ## 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. ## Re: sum matrix values (2003)

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

6. ## 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. ## Re: sum matrix values (2003)

This can happen when data are imported from another application.

8. ## 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. ## 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
•