Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
Greetings,
I am really stuck would really appreciate some help. I want to count the number of unique values in column B, but only if column A contains a value. A B x abc abc x def x x ghi Column B must also contain a value (not blank). So that means there are three unique values in column B in the above table. I was expecting to find a "count unique" function in Excel, but found nothing. Thanks! Shane Office Excel 2007 SP1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
Try this array formula** :
=COUNT(1/FREQUENCY(IF(A2:A10<"",IF(B2:B10<"",MATCH(B2:B10 ,B2:B10,0))),ROW(B2:B10)-MIN(ROW(B2:B10)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Shane Goodman" wrote in message ... Greetings, I am really stuck would really appreciate some help. I want to count the number of unique values in column B, but only if column A contains a value. A B x abc abc x def x x ghi Column B must also contain a value (not blank). So that means there are three unique values in column B in the above table. I was expecting to find a "count unique" function in Excel, but found nothing. Thanks! Shane Office Excel 2007 SP1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
=SUM(N(FREQUENCY(IF((A1:A10="x")*(B1:B10<""),MATC H(B1:B10&"",B1:B10&"",)),MATCH(B1:B10&"",B1:B10&"" ,))0))
ctrl+shift+enter, not just enter "Shane Goodman" wrote: Greetings, I am really stuck would really appreciate some help. I want to count the number of unique values in column B, but only if column A contains a value. A B x abc abc x def x x ghi Column B must also contain a value (not blank). So that means there are three unique values in column B in the above table. I was expecting to find a "count unique" function in Excel, but found nothing. Thanks! Shane Office Excel 2007 SP1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
Biff,
It worked! I have trouble understanding anything nested three or more times, so I just copies it exactly and replaced the range values. Thanks for your help. Shane. "T. Valko" wrote in message ... Try this array formula** : =COUNT(1/FREQUENCY(IF(A2:A10<"",IF(B2:B10<"",MATCH(B2:B10 ,B2:B10,0))),ROW(B2:B10)-MIN(ROW(B2:B10)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Shane Goodman" wrote in message ... Biff, It worked! I have trouble understanding anything nested three or more times, so I just copies it exactly and replaced the range values. Thanks for your help. Shane. "T. Valko" wrote in message ... Try this array formula** : =COUNT(1/FREQUENCY(IF(A2:A10<"",IF(B2:B10<"",MATCH(B2:B10 ,B2:B10,0))),ROW(B2:B10)-MIN(ROW(B2:B10)+1))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique values | Excel Discussion (Misc queries) | |||
How to count unique values? | Excel Worksheet Functions | |||
Count of Unique values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Count Unique Values | Excel Worksheet Functions |