Count Different Values in a column

K

KJ MAN

I need to know the number of differing entries in a column.
For Example

Name:
George
Carol
Don
George
Don
Carol
Pat
Sam
George

Should return 5 because there are 5 different names.
 
S

Suranthe de Silva

If the values are in the range A1:A10 then

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

The first part gives 1 for each cell that is not empty. Then divides it
with how many instances there is of this value.
Summing that gives you the "unique count".

The &"" is to avoid 0 and #DIV/0 when a cell is empty.

Ref: http://www.mrexcel.com/archive2/63500/73502.htm

Happy Programming!
- Suranthe

*** Sent via Developersdex http://www.developersdex.com ***
 
Top