Finding Sum of Unique Values for Duplicate Values

Hi
In column c use this helper formula
=countifs($a$2:a2,a2,$b$2:b2,b2) drag down
in f2 cell
=sumifs(C:C,a:a,d2,c:c,1) or countifs(c:c,1,a:a,d2)

You are welcome. If you problem has solved mark thread as solved (in Thread Tools) and you may add reputation below my avatar pic

Try this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	Cat1	1		Cat1	3
3	Cat1	1		Cat2	2
4	Cat1	2		Cat3	3
5	Cat1	2
6	Cat1	4
7	Cat1	4
8	Cat1	4
9	Cat2	3
10	Cat2	3
11	Cat2	4
12	Cat3	2
13	Cat3	2
14	Cat3	4
15	Cat3	5

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$15=D2,B$2:B$15),B$2:B$15),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Funny, I just had to solve this problem at work today. The solution I found looks like Tony's.

AZ-XL, your solution is the hardest to understand, simple formula I have seen. Would you mind explaining the concept?

Originally Posted by Jacc

AZ-XL, your solution is the hardest to understand, simple formula I have seen. Would you mind explaining the concept?

Helper column determines unique values by counting them. Values 1 "one" represents unique list. Then sumifs sums all the ones and products.

Yes but the range it's counting is different for each row because of the single $, that is what I have trouble understanding.

Tony, is the IF(xxx,1) necessary? I tried without and it seems to work fine.

Sorry for "hijacking" the thread here but I am indeed true to the topic.

Originally Posted by Jacc

Yes but the range it's counting is different for each row because of the single $, that is what I have trouble understanding.

That is right. If I will select whole column for counting formula will show total number of items. (for example 2 for first item). But I want to differentiate first occurrence with later occurrences. First occurrence will be numbered as 1 and that is what I want.

Originally Posted by Jacc

Tony, is the IF(xxx,1) necessary? I tried without and it seems to work fine.

The outer IF function is testing that the frequencies are <>0. If they are not, then they get evaluated as 1.

Thanks, AZ-XL and Tony! I'm a little bit smarter now.

Good deal. Thanks for the feedback!

You're welcome. Thanks for the feedback!