How to Count Duplicates in Excel using COUNTIF

You can use the COUNTIF function to count duplicates in Excel:

=COUNTIF(range, criteria)

Steps to Count Duplicates in Excel using COUNTIF

Step 1: Prepare the data that contains the duplicates

To start, assume that you have the following data that contains a list of names:

Name
Jon
Jon
Maria
Maria
Bill
Emma
Jon
Bill
Bill
Bill

Notice the 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 table into Excel, within the range of cells A1 to A11. You may also add a new column name called the ‘Count‘ column in cell B1:

NameCount
Jon 
Jon 
Maria 
Maria 
Bill 
Emma 
Jon 
Bill 
Bill 
Bill 

Step 3: Count the duplicates in Excel using the COUNTIF function

Recall that the COUNTIF function has the following structure:

=COUNTIF(range, criteria)

For our example, apply the COUNTIF function in cell B2 to get the count of duplicates:

=COUNTIF(A:A, A2)

Where:

  • A:A is the column (column A) where all of the values are currently stored
  • A2 is the cell of the first value (‘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:

NameCount
Jon3

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:

NameCount
Jon3
Jon3
Maria2
Maria2
Bill4
Emma1
Jon3
Bill4
Bill4
Bill4

Leave a Comment