Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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!

Check out our Excel Resources

Re: Counting Distinct Values

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.