Count Unique Values from a Subset of a List
 

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

Count Unique Values from a Subset of a List
 

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?

Count Unique Values from a Subset of a List
 

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)


--
Biff
Microsoft Excel MVP


"Ricardo Dinis" wrote in message
...
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

Count Unique Values from a Subset of a List
 

On Jan 16, 10:11 pm, "T. Valko" wrote:
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)

--
Biff
Microsoft Excel MVP

"Ricardo Dinis" wrote in message

...

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

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.

Count Unique Values from a Subset of a List
 

"Ricardo Dinis" wrote in message
...
On Jan 16, 10:11 pm, "T. Valko" wrote:
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)

--
Biff
Microsoft Excel MVP

"Ricardo Dinis" wrote in message

...

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

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!

--
Biff
Microsoft Excel MVP

Count Unique Values from a Subset of a List
 

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