How to Count Unique Names in Excel (With Example)


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

Leave a Reply

Your email address will not be published. Required fields are marked *