How to Count Duplicates in Excel using COUNTIF

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.