how can i count the number of unique names in a list

A

Anvil22

I hace a list with multiple same names. in excel how can i count each name
once for a get a list total . I tried most of the count function and can get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you
 
R

Ron Coderre

This formula returns the count of unique items:

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

Adjust range references to suit your situation.

In your posted example, 5 names are listed, but smith is listed twice.
Consequently, there are 4 unique names.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
R

Ron Coderre

Not that it's important...but, my comment should have been:

In your posted example, 6 names are listed,
but smith and west are listed twice.
Consequently, there are 4 unique names.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
A

Anvil22

Ron,
It worked - greatly appreciated- thank you.

Ron Coderre said:
Not that it's important...but, my comment should have been:

In your posted example, 6 names are listed,
but smith and west are listed twice.
Consequently, there are 4 unique names.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
R

Ron Coderre

You're very welcome......I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top