While dealing with a large amount of raw data in MS Excel, we often face duplicate cells, rows, or columns. Sometimes it can be helpful if we can count how many duplicate rows, cells, or columns are there. However, we are going to discuss the methods how to count the duplicate rows in Excel only in this tutorial with suitable examples and proper illustrations.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**4 Methods to Count Duplicate Rows in Excel**

Let’s consider a dataset of a fruit shop consisting of Customer’s **Name**, **Product**, and **Bill**.

Now, we will discuss 4 methods to count duplicate rows from any Excel dataset.

**1. COUNTIF Function to Count Duplicate Rows in Excel**

**The COUNTIF function** is one of the statistical functions. It counts the number of cells that meet a criterion.

**Syntax:**

**=COUNTIF(range,criteria)**

**Arguments:**

**– It is the part from where the function will count.**

*range***– It is the criteria to search from the range.**

*criteria***1.1 COUNTIF With the 1st Occurrence**

**Step 1:**

- We will add a table to show the duplicates count.
- The table shows the duplicates of product and their count.

**Step 2:**

- Go to
**Cell C13.** - Type the
**COUNTIF**function. - In the argument section, we will search
**B13**in the range**B5:B10**and count if found. The formula is:

`=COUNTIF($B$5:$B$10,B13)`

**Step 3:**

- Now, press
**Enter.**

**Step 4:**

- Pull the
**Fill Handel**icon to the last cell.

Finally, we get the results with the 1st occurrence.

**Read More:** **How to Count Duplicates Based on Multiple Criteria in Excel**

**1.2 COUNTIF Without 1st Occurrence**

**Step 1:**

- Modify the previous formula.
- Subtract 1 from that formula and the formula will look like this:

`=COUNTIF($B$5:$B$10,B13)-1`

**Step 2:**

- Now, press
**Enter.**

**Step 3:**

- Pull the
**Fill Handle**icon to the last cell.

The third column will show the results without 1st occurrence. Also, one interesting thing is that we can easily say which item is unique. From the result where showing **0**, means that item is not duplicated.

**Note:** Keep in mind that this method cannot identify case-sensitive objects.

**Read More:** **How to Count Occurrences Per Day in Excel (4 Quick Ways)**

**2. SUM + EXACT Functions to Count Duplicate Rows (Case-Sensitive Approach)**

**The SUM functions** used for summation. The sum objects may be numbers, cell references or range, or the combination of all.

**Syntax:**

**SUM(number1,[number2],…)**

**Argument:**

**– The first number we want to add. The number can be like 4, a cell reference, or a cell range.**

*number1***– This is the second number we want to add. We can add up to 255 numbers.**

*number2-255***The EXACT function** compares two text strings. And returns **TRUE **if they are the same, **FALSE **otherwise. **EXACT **is case-sensitive. We use **EXACT **to test text being entered into a document.

**Syntax:**

**EXACT(text1, text2)**

**Argument:**

**– The first text string.**

*text1***– The second text string.**

*text2*In this section, we will use the combination of the **SUM **and **EXACT **functions to count duplicates with case sensitivity.

**Step 1:**

- First, check the data if there is any case-sensitive data in
**Column B**. - We see that Joe and joe are two case-sensitive data that exist on our sheet.

**Step 2:**

- Go to
**Cell C13**and write the formula.

`=SUM(--EXACT($B$5:$B$10,B13))`

**Step 3:**

- Now, press
**Enter**.

Notice that Joe is showing **1 **on the count box.

**Step 4:**

- Pull the
**Fill Handle**icon to the last cell.

Finally, we get the result with case sensitivity. Liz is showing **2** as no case difference exists, Joe is 1 because of case difference.

**Formula Breakdown:**

**=SUM(–EXACT($B$5:$B$10,B13))**

**EXACT($B$5:$B$10,B13)**This function will search for the exact matching of**B13**in the range**B5:B10**. If found will show**TRUE**otherwise**FALSE**for each of the cells.**Output**:`{FALSE; FALSE; FALSE; FALSE;FALSE;TRUE}`

**SUM(–EXACT($B$5:$B$10,B13))**This will sum the**TRUE**outputs of the exact function.**Output:**1

**Read More:** **How to Count Duplicates in Column in Excel (3 Ways)**

**Similar Readings**

**How to Count Duplicates in Two Columns in Excel (8 Methods)****Count Duplicates in Excel Pivot Table (2 Easy Ways)****How to Count Duplicate Values in Multiple Columns in Excel (6 Ways)**

**3. COUNTIFS Function to Count Duplicate Rows**

**The COUNTIFS Function **contains conditions, apply those conditions among the specified range. Then counts how many times those conditions are fulfilled.

**Syntax:**

**COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)**

**Arguments****:**

**– This is the first range where the conditions will be applied.**

*criteria_range1***– This is the condition that will be applied to the first range.**

*criteria1***– The additional ranges and corresponding conditions.**

*criteria_range2, criteria2, …*In this section, we will apply the **COUNTIFS **function that will count duplicate rows.

**Step 1:**

- We modified the data to apply the
**COUNTIFS**and show the duplicated rows.

**Step 2:**

- Go to
**Cell C13**. - Write the
**COUNTIFS**function. - Complete the formula and the formula will look like as:

`=COUNTIFS($B$5:$B$10,B13,$C$5:$C$10,C5,$D$5:$D$10,D5)`

**Step 3:**

- Now press
**Enter**.

We can see that result is **0**, as this combination is not duplicated in our sheet.

**Step 4:**

- Pull the
**Fill Handle**icon to the last cell.

In the case of Liz get **2** because this data is duplicated, and the rest data are unique row-wise.

**Read More:** **VBA to Count Duplicates in Range in Excel (4 Methods)**

**4. Total Duplicate Rows in Excel**

In this section, we will count the duplicate rows in a column.

**4.1 Using COUNTIF function**

**Step 1:**

- We add a column named
**Duplicate**in our data to show which data is duplicated or not.

**Step 2:**

- Now, write the
**COUNTIF**function in**Cell E5**. The formula is:

`=IF(COUNTIF($B$5:B5,B5)>1,"Yes","N/A")`

**Step 3:**

- Now, press
**Enter**.

**Step 4:**

- Drag the
**Fill Handle**icon to**Cell E10**.

We get **Yes** for duplicated data and **N/A** for the unique and 1st occurrences.

**Step 5:**

- Add a row in the data set named
**Total Duplicates**.

**Step 6:**

- Apply the
**COUNTIF**function to count the**Yes**.

**Step 7:**

- Now, hit
**Enter**and get the result.

Now, we can say that we have two duplicated data on the mentioned column.

**4.2 Combination of COUNTIF with Other Functions**

**The ROWS function** returns the number of rows in a reference or array.

**Syntax:**

**ROWS(array)**

**Argument:**

**An array, an array formula, or a reference to a range of cells for which we want the number of rows.**

*array –*In this section, we will combine the **COUNTIF **function with other functions.

**Step 1:**

- Add a new row to show the
**Total Duplicates**.

**Step 2:**

- Write the formula on
**Cell D11**. The formula is:

`=ROWS($B$5:$B$10)-SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))`

**Step 3:**

- Now, hit
**Enter**.

After applying the formula, we get the total duplicates.

**Formula Breakdown:**

**=ROWS($B$5:$B$10)-SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))**

**ROWS($B$5:$B$10)**This will show the number of rows of the range.**Output**: 6

**COUNTIF($B$5:$B$10,$B$5:$B$10)**This function will search the**B5:B10**range and count how many times it was found on that range of cells. It will give a return for each cell.**Output:**{2;1;2;1;2;2}

**IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0)**It will return 1 if the previous formula return is equal to**1**, otherwise**0**. It will give a return for each cell.**Output:**{0;1;0;1;0;0}

**SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))**It will provide the sum of the**IF**function where gets**1**.**Output**: 2

**ROWS($B$5:$B$10)-SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))**This will subtract the value of the**SUM**function from the**Rows**function.**Output**: 4

**Read More: ****Excel VBA to Count Duplicates in a Column (A Complete Analysis)**

**Conclusion**

In this article, we explained 4 methods to explain how to count duplicate rows in Excel. I hope this will satisfy your needs. Please have a look at our website **exceldemy.com** and give your suggestions in the comment box.