MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum(if


Posted by Lars on August 14, 2001 10:35 AM

I need to add these numbers together using something what is the best way to do it? If the number repeats below add it to the first number listed

1101 341.32 620810.69 0
3101 307.615 113200.76 0
3102 346 145949.82 -3806
3103 407 113396.22 -4477
3104 373 151235.64 -4103
1109 379 91818.84195 0
1101 265 753598 -100


1101 is listed twice I want that rolled up and summed together so 1101 is 606.32 (341.32+265)for column b and the other columns as well.

Is this possible?
Thanks


Posted by lenze on August 14, 2001 10:51 AM

A quick and easy way would be to use the Subtotal option. Sort your date by the first column, then choose Data>Subtotals and select the options you want. After the subtotals have been inserted, use the Outline bars to change to the appropriate view.

Posted by Aladin Akyurek on August 14, 2001 10:59 AM

Lars,

Assuming that your data occupy A:D from A2 on.
First create a list of unique items from the first column next to you data, e.g, in F from F2 on (you can do this with Adv Filter, for example).

In G2 enter: =SUMPRODUCT(($A$2:$A$10=$F2)*(B2:B10))

Copy this across up to I2 then down as far as needed.

PS. Did you solve that performance problem that you reported much earlier?

Aladin

=========== 1101 341.32 620810.69 0

Posted by LARS on August 14, 2001 11:20 AM

I just deleted the formulas si it would speed up, the Array formula was slowing it down now I have just a simple lookup

Lars, Assuming that your data occupy A:D from A2 on.

Posted by Lars on August 14, 2001 11:21 AM