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:

Name | Count |

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:

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 |