Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count of Unique values
I have a list of values down a column, e.g.
a a a c c e e etc. I know I can use advanced filter to get a list of unique values and then count them, but is there a single function that would achieve this. i.e the result for the data above would be 3 (a,c & e) Rgds |
#2
|
|||
|
|||
Hi Richard,
Try... =SUMPRODUCT((YourRange<"")/COUNTIF(YourRange,YourRange&"")) "Richard" wrote in message oups.com... I have a list of values down a column, e.g. a a a c c e e etc. I know I can use advanced filter to get a list of unique values and then count them, but is there a single function that would achieve this. i.e the result for the data above would be 3 (a,c & e) Rgds |
#3
|
|||
|
|||
Hi Richard,
If you have numeric values then: =SUM(N(FREQUENCY(A1:A10,A1:A10)0)) If you have text or mixture of text and numeric and no blanks then: =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)) If you have text or mixture of text and numeric and blank cells then: =SUMPRODUCT(IF(ISNUMBER(1/COUNTIF(A1:A10,A1:A10)),1/COUNTIF(A1:A10,A1:A10))) This one has to be ARRAY-entered (Ctrl+Shift+Enter, not just Enter). Blanks are not counted. Rgards, KL "Richard" wrote in message oups.com... I have a list of values down a column, e.g. a a a c c e e etc. I know I can use advanced filter to get a list of unique values and then count them, but is there a single function that would achieve this. i.e the result for the data above would be 3 (a,c & e) Rgds |
#4
|
|||
|
|||
KL wrote:
Hi Richard, If you have numeric values then: =SUM(N(FREQUENCY(A1:A10,A1:A10)0)) If you have text or mixture of text and numeric and no blanks then: =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)) If you have text or mixture of text and numeric and blank cells then: =SUMPRODUCT(IF(ISNUMBER(1/COUNTIF(A1:A10,A1:A10)),1/COUNTIF(A1:A10,A1:A10))) This one has to be ARRAY-entered (Ctrl+Shift+Enter, not just Enter). Blanks are not counted. The last one won't succeed with formula blanks, while =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) or {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} will. BTW, whenever you need filtering with IF, it's better to replace SumProduct with Sum. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of unique entries | Excel Discussion (Misc queries) | |||
how to count unique entries with multiple condition | Excel Worksheet Functions | |||
Count Distinct Values? | Excel Worksheet Functions | |||
AutoFilter lists unique values | Excel Discussion (Misc queries) | |||
Counting unique values + frequency | Excel Worksheet Functions |