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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 02:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com