Counting different values within a column
Posted by Andrew Cooke on December 13, 2000 6:41 AM
Apoligies to all for the simplicity of the query but although I use Excel I don't use formulas that often.
A column of data eg 1,2,2,3,5,5 If I use try to use "count" formula I get an answer of 6, which is right but I want to count the number of different values ie 1 2 3 & 5 to give an answer of 4 !
All suggestions gratefully accepted, thanks in advance
Posted by Tim Francis-Wright on December 13, 2000 7:08 AM
Chip Pearson's site has a good reference on this:
www.cpearson.com/excel/duplicat.htm
Excerpts follow:
=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),
IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))
returns the number of unique entries in Range1. It will not count blanks at all.
If your data does not have any blanks in the range, you can use the following Array Formula, developed by David Hager:
=SUM(1/COUNTIF(A1:A10,A1:A10))
If your data has only numeric values or blank cells, with no text or string values, you can use the formula
=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))
to count the number of unique values. This will count only the number of unique numeric values, not including text values.
