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:

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:

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:

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: