Counting Names in a Column, Ignoring Duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to count names in a column but have the formula ignore
duplicates?

example:

Column A
John Smith
John Smith
Jane Doe
Joe Smith

the formula would give me a total of - 3

I appreciate any and all help. Thank you in advance.
 
=SUM(1/COUNTIF(A1:A10,A1:A10))
and confirm as an array formula (use CNTRL-SHFT-ENTER rather than just ENTER)

will display 3 for your example.
 
That probably means there are empty cells within the range.

Try this (normally entered, not an array):

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

Biff
 
Thank you for your reply. Your formula is counting the names but it's not
ignoring duplicate names. Any other suggestions?
 
Biff:

Why is the &"" used (at the end) below?

It still works without it (just experimenting here)..

TIA,

Jim May
 
Dave,
Your right - causing a cell to go blank does result in #DIV/0!
But I don't get how th &"" addition prevents it!!
Jim
 
The &"" will make the empty cell look like a cell that had ="" in it--it won't
be empty--just a 0 length string.
 
Back
Top