Count Unique Values from a Subset of a List

  • Thread starter Thread starter Ricardo Dinis
  • Start date Start date
R

Ricardo Dinis

Hi,

If I use the array formula bellow to count the unique values of a list
it works fine.

{=SUM(1/COUNTIF(C8:C11,C8:C11))}

But what I really need is the sum of unique values, of each subset of
the list.

A B
1 dog 1
2 dog 1
3 dog 3
4 dog 2
5 cat 1
6 cat 2

For example if I have the table above, the result that I want is the
sum of 3 different of dog subset elements plus 2 from cat subset.

Anyone can help me?

Thanks,

Ricardo
 
Like using sumif?

=SUMIF(A1:A8,"dog",B1:B8). The answer would be 7 using your numbers
above.

If dog and cat entries are in Column A and the numbers are in Column
B, this formula would give you the total in B for every entry of dog
in A.

Does that give you what you want?
 
Assuming there are no empty/blank cells in your data range...

D1 = dog
D2 = cat

Enter this array formula** in E1 and copy down to E2:

=COUNT(1/FREQUENCY(IF(A$1:A$6=D1,MATCH(B$1:B$6,B$1:B$6,0)),ROW(B$1:B$6)-MIN(ROW(B$1:B$6))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Assuming there are no empty/blank cells in your data range...

D1 = dog
D2 = cat

Enter this array formula** in E1 and copy down to E2:

=COUNT(1/FREQUENCY(IF(A$1:A$6=D1,MATCH(B$1:B$6,B$1:B$6,0)),ROW(B$1:B$6)-MIN(ROW(B$1:B$6))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
Click to expand...

Thanks a lot, It's does exactly what I'm looking for.

HKaplan, I dont want to sum dog elements, I was trying to count unique
values. Sorry my bad English if you don't understand.
 
Ricardo Dinis said:
Thanks a lot, It's does exactly what I'm looking for.

HKaplan, I dont want to sum dog elements, I was trying to count unique
values. Sorry my bad English if you don't understand.
Click to expand...

You're welcome. Thanks for the feedback!
 
Biff, would you mind explaining how this formula works? Evaluating it piece by piece provides some understanding but not 100%. Thanks!
 
Back
Top