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