Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values | Excel Worksheet Functions | |||
Unique Count when Values 0.01 | Excel Worksheet Functions | |||
How to count unique values? | Excel Worksheet Functions | |||
Count unique values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions |