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.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Calculate Relative Frequency in Excel

How to Count Frequency of Text in Excel

How to Calculate Cumulative Frequency in Excel