You can use the following formula to count the number of unique names in a range in Excel:
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A12,A2:A12,0),ROW(A2:A12)-ROW(A2)+1)>0))
This particular formula counts the number of unique names in the range A2:A12.
To get a list of each unique name, you can use the UNIQUE function:
=UNIQUE(A2:A12)
This will return a list of unique names in the range A2:A12.
The following example shows how to use each of these formulas in practice.
Example: How to Count Unique Names in Excel
Suppose we have the following dataset that contains information about sales made by various employees at some company:
Suppose we would like to count the number of unique names in column A.
We can type the following formula into cell E2 to do so:
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A12,A2:A12,0),ROW(A2:A12)-ROW(A2)+1)>0))
The following screenshot shows how to use this formula in practice:
The output tells us that there are 6 unique names in column A.
To actually view a list of these unique names, we can type the following formula into cell E2:
=UNIQUE(A2:A12)
The following screenshot shows how to use this formula in practice:
The formula returns the six unique names in column A:
- Andy
- Bob
- Chad
- Derrick
- Eric
- Frank
Each of these names occurs at least once in column A and some of the names occur multiple times.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count If Cells Contain Text
Excel: How to Count Unique Values by Group
Excel: How to Count Unique Values Based on Multiple Criteria
