Excel: How to Count Duplicate Values Only Once


You can use the following formula in Excel to count the unique values in a particular range while only counting duplicate values once:

=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))

This particular formula will count the number of unique values in the range A2:A12 while only counting duplicate values once.

The following example shows how to use this formula in practice.

Example: Only Count Duplicate Values Once 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 and only count duplicate values once.

We can type the following formula into cell E2 to do so:

=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))

The following screenshot shows how to use this formula in practice:

Excel count duplicate values only once

The output tells us that there are 6 unique names in column A.

This formula was able to count the number of unique names while only counting duplicates once.

For example, there were four cells equal to “Frank” but only one of these “Frank” cells was actually counted.

To actually view a list of the six 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 Find Duplicates Using VLOOKUP
Excel: How to Remove Duplicates and Keep First One
Excel: How to Remove Duplicate Rows Based on Two Columns

Leave a Reply

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