Sum unique values under criteria

**depple** · 04-16-2009, 04:36 AM

First, I am sorry for not abiding to the forum rules when asking questions in another member's thread.

With reference to a thread that is nearly similar to my own problem:

http://www.excelforum.com/excel-gene...ml#post2076546

I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the C-column. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"".

Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.

**windknife** · 04-16-2009, 05:08 AM

I try to solve it as attached. You can check it.

**depple** · 04-16-2009, 05:43 AM

Hi,

Thanks for the response! I have looked at it, but it's not exactly what I am looking for. In your attached file, there could be several managers. I have attached an edited version of yours. I would like to toggle between "HUMPTY" and "DUMPTY" and get the sum. Now it seems to only sum unique non-zero values, regardless of manager.

Hope this explains my needs.

**DonkeyOte** · 04-16-2009, 05:57 AM

Do you just want a SUMIF ?

=SUMIF(A:A,F4,C:C)

**depple** · 04-16-2009, 06:11 AM

No, because a SUMIF would also include duplicate values (in this example row 6 & 7). The thread that I initially linked to nearly fixed my problem, but it did sum in the zero in row 8, and not the "18400" in row 9 that I want it to. So basically: The solution from the thread linked to, but NOT regarding the rows in column C with values = 0.

**DonkeyOte** · 04-16-2009, 06:31 AM

The example was not a good one unfortunately as there were no duplicates to exclude hence my question re: SUMIF...

Adapating NBVC's FREQUENCY array from the other thread perhaps this will work for you ?

=SUM(IF(FREQUENCY(IF($A$2:$A$9=F2,IF($B$2:$B$9<>"",MATCH($B$2:$B$9&$C$2:$C$9,$B$2:$B$9&$C$2:$C$9,0))),ROW($B$2:$B$9)-ROW($B$2)+1),$C$2:$C$9))
Committed with CTRL + SHIFT + ENTER

the above is basically summing C for a given manager wherever the concatenated values of B & C are unique... I am not sure if this is what you want or not ?