Sumproduct on unique entries

  • Thread starter Thread starter Greg
  • Start date Start date
G

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
 
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)
 
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)
 
It would help if you gave us more info. Maybe?
=sumproduct((a2:a200=b2)*(b2:b200=c2)*c2:c200)
 
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
 
Back
Top