You can use the COUNTIF function to count duplicates in Excel:
=COUNTIF(range, criteria)
In this short post, you’ll see a simple example with the steps to count duplicates for a given dataset.
Steps to Count Duplicates in Excel using COUNTIF
Step 1: Prepare the data that contains the duplicates
To start, let’s say that you have the following dataset that contains a list of names:
| Name |
| Jon |
| Jon |
| Maria |
| Maria |
| Bill |
| Emma |
| Jon |
| Bill |
| Bill |
| Bill |
As you can observe, there are duplicate names in the table. The goal is to count the number of times each name is present in the table.
Step 2: Copy the data into Excel
For simplicity, copy the above table into Excel, within the range of cells A1 to A11. You may also add a new column called the ‘Count’ column in cell B1:
| Name | Count |
| Jon | |
| Jon | |
| Maria | |
| Maria | |
| Bill | |
| Emma | |
| Jon | |
| Bill | |
| Bill | |
| Bill |
You can then apply the COUNTIF function under the ‘Count’ column to get the count of duplicates.
Step 3: Count the duplicates in Excel using the COUNTIF function
Recall that the COUNTIF function has the structure of:
=COUNTIF(range, criteria)
In the context of our example, you’ll need to apply the COUNTIF function in cell B2:
=COUNTIF(A:A,A2)
Where:
- A:A is the column where all of our values are stored (in our case it is column A)
- A2 is the cell where we have our first value (in our case it is ‘Jon’)
The COUNTIF function will then count the number of times that ‘Jon’ appears in Column A. For our example, the name ‘Jon’ appears 3 times:
| Name | Count |
| Jon | 3 |
To apply the COUNTIF function across all the names, drag the function from cell B2 to cell B11. You’ll then get the count of all the duplicates:
| Name | Count |
| Jon | 3 |
| Jon | 3 |
| Maria | 2 |
| Maria | 2 |
| Bill | 4 |
| Emma | 1 |
| Jon | 3 |
| Bill | 4 |
| Bill | 4 |
| Bill | 4 |
While we used a small sample of data, the same principles that we just covered can be applied for much larger datasets.