# How to Count Duplicates in Excel (With Examples)

Often you may want to count the number of duplicate values in a column in Excel.

Fortunately this is easy to do and the following examples demonstrate how.

### Example 1: Count Duplicates for Each Value

We can use the following syntax to count the number of duplicates for each value in a column in Excel:

```=COUNTIF(\$A\$2:\$A\$14, A2)
```

For example, the following screenshot shows how to use this formula to count the number of duplicates in a list of team names: From the output we can see:

• The team name ‘Mavs’ occurs 2 times
• The team name ‘Hawks’ occurs 3 times
• The team name ‘Nets’ occurs 4 times

And so on.

### Example 2: Count Non-Duplicate Values

We can use the following syntax to count the total number of non-duplicate values in a column:

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

For example, the following screenshot shows how to use this formula to count the number of non-duplicates in a list of team names: From the output we can see that there are 6 unique team names.

### Example 3: List Non-Duplicate Values

We can use the following syntax to list out all of the non-duplicate values in a column:

`=UNIQUE(A2:A14)`

The following screenshot shows how to use this formula to list out all of the unique team names in a column: We can see that there are 6 unique team names and each of them are listed in column C.