Finding out the number of duplicate rows using COUNTIF formula

Say you have a column with duplicate rows. You just want to find out which rows are duplicate and how many times they are duplicated. You can find out the duplicate ones with the COUNTIF formula. We are going to show how to use the COUNTIF formula and how to find out the duplicate ones using this formula. Now this system, I mean using the COUNTIF formula works with the values cells contain, not the formatted values.

Read more: How to Highlight Duplicate Rows in Excel

COUNTIF formula syntax: COUNTIF (range, criteria)

The Range is the range of cells from where you want to find your values.

The Criteria can be expressed in many ways. It can be number, expression, cell reference, or text to be fulfilled.

An Example of the COUNTIF formula: COUNTIF(A1: D5, ">250"): how you will read this? Simple: Count the value if it is greater than 250 in the cell range A1 to D5.

Observe the following image. We have applied this formula: (COUNTIF(A1: D5, ">250") ) in cell E7 and E7 displays the total numbers that are greater than 250 in the cell range A1 to D5.


COUNTIF(A1: D5, ">250") the formula we are applying in Cell E7.

Click here to know more about the COUNTIF formula.

Finding out the number of duplicate rows using the COUNTIF formula

We shall apply the COUNTIF formula to find out the duplicate rows in a column or table. In our example, we have used a column.

First of all, let’s see the inside view of our example file. See the image below.

Using COUNTIF formula to find out the duplicate rows.

Using COUNTIF formula to find out the duplicate rows. Inside data of our example.

  1. Click the B1 cell to select it. Write this formula in this cell: “=COUNTIF (A: A, A1)”. A: A means column A and criteria A1 means: the value of cell A1. In one sentence the whole command is: Count in the value if the value is equal to A1 in column A.
  2. We shall now copy this formula (“=COUNTIF (A: A, A1)”) into cell B2 to B23 using relative cell references. To do this, select cell B1. A square box will be shown in the bottom-right corner of cell B1, it is called Fill Handle. Click Fill Handle, hold it, and grab until you reach cell B23.
  3. Release the mouse button and you will get the following image.
Finding out duplicates using COUNTIF formula.

See the image. Some values are greater than 1. 4 is displaying after Accounting, which means Accounting has been duplicated 4 times in column A.

Download Working File

Download the working file from the link below:


Read More



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply