Table of Contents
Counting duplicates in Excel can be done using the COUNTIF function. This function allows you to count the number of cells in a range that contain a specified value or condition. For instance, if you want to count the number of duplicates in a column of data, you can use the COUNTIF function with the condition of “=A1”, which will count the number of cells in the range that have the same value as A1. You can also use the COUNTIF function to count the number of cells in a range that contain a specific value, such as the word “apple”, or a numerical range, such as “>10”.
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
=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.