Sum unique values according to criteria (sumif unique values)

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:

Please Login or Register  to view this content.

Many thanks on advance for all your help.

Cheers, hmpw

Originally Posted by 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.

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

Originally Posted by Ace_XL

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

Originally Posted by 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

Please Login or Register  to view this content.

and substitute the sumproduct in G2 with

Please Login or Register  to view this content.

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