How to count same text in Excel

You can use theCOUNTIF function to count duplicates in Excel:

In this short post, youll see a simple example with the steps to count duplicates for a given dataset.

Steps toCount Duplicates in Excel using COUNTIF

Step 1: Prepare the data that contains the duplicates

To start, lets say that you have the following dataset that contains a list of names:

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:

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:

In the context of our example, youll need to apply the COUNTIF function in cell B2:

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:

To apply the COUNTIF function across all the names, drag the function from cell B2 to cell B11. Youll then get the count of all the duplicates:

While we used a small sample of data, the same principles that we just covered can be applied for much larger datasets.