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:
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
