Count Unique Values from a Subset of a List

[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

 

[HKaplan]

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?

 

[T. Valko]

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)

 

[Ricardo Dinis]

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)

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.

 

[T. Valko]

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.

You're welcome. Thanks for the feedback!

 

[mikebeheshti]

Biff, would you mind explaining how this formula works? Evaluating it piece by piece provides some understanding but not 100%. Thanks!