Hi,

I have a question. Lets say i have an excel with some data. I need to count in a column(or a row) all the unique entries.

for example
Name
1 John
2 george
3 Merlin
4 Darko
5 George
6
7 Pikachu
8 Superman
9 Drado



I want to count how many Unique names appear on the excel, not the total number of names. What is the exact formula?

Thank you

Please can you clarify what you mean by "unique"?


Do you want a count of the names which only appear once:

John
John
Peter
Bob

would give a count of 2, because John appears more than once so the unique names are Peter and Bob.



....or do you want a count of distinct names?

John
John
Peter
Bob

Would give a count of 3, because there are 3 distinct names in the list: John, Peter, Bob.

__________________
Colin

RAD Excel Blog

I want to count how many Unique names there are.

for example how many different names appear at least once

That means you want to count the distinct items, not the unique ones.

There are several formulas which can be used to do this, but none of them are simple. The most commonly used one, as outlined here, is:

This is an array formula so when you type it into the formula bar you have to complete the entry with CTRL+SHIFT+ENTER, not just ENTER. You will also have to adjust the A1:A100 references to the range containing your names, and A1 to the first cell in the range containing your names.

If you prefer I can direct you to a VBA UDF which will make the formula simpler.

__________________
Colin

RAD Excel Blog