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