Sumproduct on unique entries

[Greg]

Hi,

I have two columns of data, one is a criterion and the
other is its value.

However, I want to sum only based on the unique criterion
since I have duplicates in criterion column.

How would I tweak sumproduct formula, or if there is
another one?

Thanks,
Greg

 

[Bob Phillips]

Greg,

Do you mean

=SUMPRODUCT(--(COUNTIF(A1:A100,A1:A100)=1),B1:B100)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

That would mean that all values with the same criterion would be
the same. i.e.

a 2
b 4
a 2
c 6
a 2
e 8

meaning that duplicate "a" all have the same value in B
and not

a 2
b 4
a 6
c 8
a 10
e 12

since you wouldn't know which value in B to retrieve for "a"

=SUMPRODUCT(--(A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""),B2:B10)

 

[Don Guillett]

It would help if you gave us more info. Maybe?
=sumproduct((a2:a200=b2)*(b2:b200=c2)*c2:c200)

 

[Greg]

Peo,

You are exactly right, all values are the same for the
same criterion. Sorry for not being clear.
Thanks a lot for the formula, it does the trick.
Best regards,
Greg