Counting Distinct Values

Posted by Richard on January 30, 2002 9:10 AM

I have a column of names:
Dick
Joe
Bob
Dick
Harry
Joe
in this cell, I want a count of the number of unique names in the cells above (4 in this example). How? THANKS!

Posted by Steve Hartman on January 30, 2002 9:28 AM

If your data above is in cells A1 through A6:

=SUM(IF(LEN(A1:A6),1/COUNTIF(A1:A6,A1:A6)))

hit Control-Shift-Enter at the same time to enter this formula.