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:

count duplicates in Excel

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

Leave a Reply

Your email address will not be published.