Counting Names in a Column, Ignoring Duplicates

[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.

 

[Guest]

=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.

 

[Guest]

This isn't working. When I enter the formula I get a "DIV/0" error.

 

[T. Valko]

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

 

[Guest]

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

 

[JMay]

Biff:

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

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

TIA,

Jim May

 

[Dave Peterson]

Try leaving some empty cells in A1:A10.

Biff:

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

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

TIA,

Jim May

 

[JMay]

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

 

[Dave Peterson]

The &"" will make the empty cell look like a cell that had ="" in it--it won't
be empty--just a 0 length string.