Sum unique values according to criteria (sumif unique values)

[hmpw]

I am looking for a formula that given a condition in one column, sums all the unique values from another column.

SUMIF only takes me half way, as I still need to sum the unique values. If I could invent functions, I guess I would be looking for something like:
SUM.UNIQUE.IF(Range to look, Criteria, Range to sum unique values)

Here's some example data:

sumif_unique_values.xlsx

Product GlobalSum
PT1 2
PT2 4
PT1 2
PT2 4
PT3 4
PT1 3
PT2 5
PT3 3
PT1 3
PT2 5
PT1 3
PT2 5

Expected results from example data:
Sum all unique values in column B (GlobalSum) for Product 1 (PT1) = 2+3 = 5
Sum all unique values in column B (GlobalSum) for Product 2 (PT2)= 4+5 = 9

I tried the solution on another post that seemed to describe my problem, but it didn't work… Here's the code sugested on that post:

=SUM(IF(ISNUMBER(1/FREQUENCY(IF(B2:B13<>"",IF(A2:A13<>"",MATCH(A2:A13,A2:A13,0))),ROW(A2:A13)-ROW(A2)+1)),B2:B13))

Many thanks on advance for all your help.

Cheers, hmpw

[MarvinP]

Hi hmpw and welcome to the forum,

If you look at advanced filters there is a "unique" check box that will reduce your table to unique values. Then use your formula or perhaps a Pivot Table to get the attached answer.

[hmpw]

Hi hmpw and welcome to the forum,

If you look at advanced filters there is a "unique" check box that will reduce your table to unique values. Then use your formula or perhaps a Pivot Table to get the attached answer.

Hi MarvinP,

Thanks for welcome me to the forum and for your answer. Your suggestion does the trick, but I didn't mention in my original post that I need it to work for Excel 2003 and more importantly, I need it to work as a formula since it goes into at least 30 cells with that many product variations in a summary report table.

Thanks anyway for your time and help.

Cheers, hmpw

[Ace_XL]

Alternatively, use a helper column..(Column 'C') of attached.

[hmpw]

Alternatively, use a helper column..(Column 'C') of attached.

Hi Ace_XL,

Thanks for your answer. Your suggestion worked like a charm. Since I failed to mention that I need it to work for Excel 2003, I had to retrofit your solution for that version, i.e., change IFERROR for IF(ISERROR(... and COUNTIFS for SUMPRODUCT, resulting in a less elegant formula, but still equally functional.

Thanks for your time and help.

Cheers, hmpw

[Marcol]

Maybe a simpler helper column?

I've used SUMPRODUCT() in this example, but using COUNTIFS()/SUMIFS() would be more efficient with 2007 and above.

Maybe someone will convert this for you.

[EDIT]
Try in D2

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)

and substitute the sumproduct in G2 with

SUMIFS($B$2:$B$13,$D$2:$D$13,1,$A$2:$A$13,$F2)

I can't check this edit, I only have access to 2003 at the moment.

[hmpw]

Maybe a simpler helper column?

I've used SUMPRODUCT() in this example, but using COUNTIFS()/SUMIFS() would be more efficient with 2007 and above.

Maybe someone will convert this for you.

[EDIT]
Try in D2

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)

and substitute the sumproduct in G2 with

SUMIFS($B$2:$B$13,$D$2:$D$13,1,$A$2:$A$13,$F2)

I can't check this edit, I only have access to 2003 at the moment.

Hi Marcol,

Thanks for your answer. Your suggestion works perfectly, but I opted for Ace_XL's solution as it needed one less helper column and I had to repeat this process in nine different places in my database. But I am still studying your formulas for general knowledge as it contains functions that I've never used before.

Thanks for your time and help.

Cheers, hmpw

[icestationzbra]

methods without any helper column:

if you are using 2007, see if this works in cell E15, just ENTER:

=SUMPRODUCT(($A$2:$A$13=$D15)*($B$2:$B$13)*(1/COUNTIFS($B$2:$B$13,$B$2:$B$13,$A$2:$A$13,$A$2:$A$13)))

if using 2003, see if this works in cell E15, CTRL+SHFT+ENTER:

=SUM(IF(FREQUENCY(IF($A$2:$A$13=$D15,$B$2:$B$13),IF($A$2:$A$13<>"",$B$2:$B$13))>0,$B$2:$B$13))

[Marcol]

Hmm?

but I opted for Ace_XL's solution as it needed one less helper column

The solution I offered only has one helper column! It will also work for all versions of Excel.
The formula in Column F is only a way to get unique values from Column A, are you considering this a helper column?

@ icestationzbra
Array formulae are very clever but can be expensive in larger lists.
Your solution is only reliable if the range calculated is the same size, or one row more, than the data list.
If the user wants to allow some freeboard for varying sizes of data lists, then you will need to use Dynamic Named Ranges, or adjust the formula for each list.

Clear Cells A12:B13 in this workbook to see what I mean.