While dealing with a large amount of raw data in Microsoft Excel, we often find duplicate cells, rows, or columns. Sometimes we face the necessity to sort duplicate rows, cells, or columns. However, we are going to discuss how to count duplicate rows in Excel with 6 ideal examples and proper illustrations in this article. I hope it will be helpful for you.

## How to Count Duplicate Rows in Excel: 6 Ideal Examples

There are many simple and effective ways to count duplicate rows in Excel. Based on the situation, I am going to explain 6 ideal examples to count duplicate rows. Let’s dive into the details.

### 1. Use COUNTIF Function to Count Duplicate Rows for Single Condition

With the help of this function, we can count duplicate rows in both considering and ignoring the first occurrence.

#### 1.1 Count Duplicate Rows Considering First Occurrence

With a simple formula of **the COUNTIF function**, we can count duplicate rows considering the first occurrence.

Here, we have a dataset with the customer’s names and product details. With the following formula in cell **C17**, we can count the duplicate rows based on the customer’s names considering the first occurrence.

`=COUNTIF($B$5:$B$13,B17)`

Here, the **COUNTIF **function counts the duplicate rows in the range **$B$5:$B$13 **based on the value in cell **B17**.

Then, use **Fill Handle **to **AutoFill **the rest cells in column **C **to see the number of duplicate rows.

#### 1.2 Count Duplicate Rows Ignoring First Occurrence

To count the duplicate rows based on the customer’s names ignoring the first occurrence, apply the following formula in cell **D17**.

`=COUNTIF($B$5:$B$13,B17)-1`

You can **AutoFill **the rest cells in column **D **with **Fill Handle **quite easily.

**Read More:** How to Count Duplicates in Column in Excel

### 2. Use COUNTIFS to Count Duplicate Rows for Multiple Conditions

It is possible to count duplicate rows with **the COUNTIFS function **based on multiple conditions. Here, we have considered the **Device **and **Country of Origin **values to count the duplicate rows. Just write the following formula in cell **D17** and press **Enter **to have the result.

`=COUNTIFS($C$5:$C$13,B17,$E$5:$E$13,C17)`

Here, the **COUNTIFS **function considers the values in cells **B17 **and **C17 **and counts the duplicate rows that match in the range **$B$5:$B$13 **and **$C$5:$C$13**.

Finally, **AutoFill **the rest of the cells in column **D **with **Fill Handle**.

**Read More:** How to Count Duplicates in Two Columns in Excel

### 3. Count Total Duplicate Rows in Excel

In order to count the total duplicate rows in Excel, we can use the **COUNTIF **function too. But this time it will need the help of other functions to have the total duplicate count.

#### 3.1 Use IF and COUNTIF Functions

To find the duplicate rows based on the customer’s names, we can use the following formula.

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

Here, the **COUNTIF **function will count the number of matched cells in the range **B5:B13 **based on the value in cell **B17**. If the result gets more than **1**, the output will be **Yes **as we have applied **the IF function**, otherwise **N/A**.

**AutoFill **the rest cells in column **C**.

Then, apply the following formula in cell **C22 **to have the total count of the duplicate rows.

`=COUNTIF(C17:C21,"Yes")`

#### 3.2 . Use COUNTIF with IF, SUM, and ROWS Functions

To have the total count of the duplicate rows in Excel, you can also apply the following formula combined with the **COUNTIF **with **IF, SUM, **and **ROWS **functions.

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

Here, the **ROWS **function returns the total number of rows and the combination of the **IF, SUM, **and **COUNTIF **functions returns the total number of rows having no duplicate row.

### 4. Use SUM & EXACT Functions to Count Case Sensitive Duplicate Rows

Based on the customer’s names, we can count the duplicate rows by applying the following formula consisting of the **SUM **and **EXACT **functions. A very important thing to keep in mind is that the **EXACT **function is very sensitive as it considers the same character differently in capital and small forms.

`=SUM(--EXACT($B$5:$B$13,B17))`

** **Here, the **EXACT **function finds the exact match value in range **B5:B13 **considering the value in cell **B17**. Then, the **SUM **function returns the summation of the number of duplicate rows.

**Read More:** How to Count Duplicate Values in Multiple Columns in Excel

### 5. Count Duplicate Rows by Extracting Unique Values with UNIQUE Function

We can ignore the duplicate values and find the unique ones by using **the UNIQUE function**. We can apply the following formula in cell **B17** to extract the unique values.

`=UNIQUE(B5:B13)`

Then, based on the unique values, we can find the number of each value in a specific range with the **COUNTIF **function. In cell **C17**,

`=COUNTIF($B$5:$B$13,B17)`

Finally, **AutoFill **the rest of the cells in column **C **with **Fill Handle**. We can see the duplicate values as well as the duplicate rows.

### 6. Use Pivot Table to Count Duplicate Rows in Case of Large Excel Dataset

In case of a large amount of data, we can use the **Pivot Table **to count the duplicate rows in Excel. For this, we need to create a **Pivot Table **first. Follow the following sequence of commands to create a **Pivot Table**.

**Select Data —> Insert Tab —> PivotTable —> From Table/Range**

A wizard named **PivotTable from table or range **will appear.

Select the **Existing Worksheet **and define a cell (i.e. **B16**). Click on **OK** to have the **Pivot Table**.

Now, drag the **Customer **option in the **PivotTable Fields **to the **Rows **and **Values **sections. We will have the **Pivot Table **in the defined location with the count of the duplicate rows.

## How to Filter Duplicate Rows with Filter Feature in Excel

We can filter duplicate rows with the help of the **Filter **feature quite easily. For this, create a separate column **G** and apply the following formula in cell **G5 **to identify the duplicate rows.

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

Now, select the entire dataset and click on **Filter **from the ribbon under **Data**.

Now, click on the arrow in the **Duplicate **column and select the **Yes **option. Click **OK **to finish the process.

Finally, we have the duplicate rows filtered with the help of the **Filter **feature.

## Things to Remember

- Always use the
**Absolute Cell Reference ($)**to**Block**the range - Use the unary operator (
**– –**) to transform the result of the**EXACT**function to an array of**0**and**1**’s.

## Frequently Asked Questions

**1. How to select duplicates in Excel?**

In order to select all the duplicates after filtering them, use the ** CTRL + A ** command.

**2. How to clear or remove duplicates in Excel?**

After filtering the duplicate rows, select all of them. Then, right-click on the mouse and select **Delete rows **from the available options to clear or remove duplicates in Excel.

**3. How to highlight duplicates in Excel?**

In order to highlight duplicates in Excel, select all the filtered duplicates. Then, go to the **Home **tab and select a color from **Fill **to highlight duplicates.

**4. How to copy or move duplicates to another sheet?**

First of all, select all the filtered duplicate cells and press ** CTRL + C **** **to copy them. Then, pick a suitable location and press ** CTRL + V **** **to copy or move the duplicates.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

In this article, I have tried to explain how to count duplicate rows in Excel with 6 ideal examples. I have also added the important things to keep in mind and frequently asked questions. I hope this article will be helpful for you. For any further questions, please comment below.

**Related Articles**

- Count Number of Occurrences of Each Value in a Column in Excel
- Count the Order of Occurrence of Duplicates in Excel
- How to Count Duplicates Based on Multiple Criteria in Excel
- How to Count Occurrences Per Day in Excel

**<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel**

1. You could simplify the last formula, by removing the whole part about =ROWS(), using:

IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =2,1,0).

All it took was to use “=2” instead of “=1”.

(Note. if your purpose was to explain the use of ROWS, then great).

2. What I am looking for is total COUNT of whole (or part) row duplicates.

eg. Liz and Apple, Joe and Apple, Joe and Orange (total of how many such rows are repeated) BUT – WITHOUT creating any new columns, which would be too easy. In your table above, the answer would be “1” because ONLY Liz and Apple is repeated. I assume this would require COUNTIFS, possibly an array. But can’t get it to work. Please let me know if you have anything on this. (Note. I often share links to your site, when I help friends in different countries with Excel)

Hi Alex, it is a great pleasure for us to know that you are getting benefits from our content (and referring us to your friends too)!

Now, your answers:

1. No, you have to use the

ROWSfunction too. Otherwise, you will not get the correct answer in other cases (your guess is true only for this particular set of data).2. Yes. You are right. You have to use the

COUNTIFSfunction. Use the following formula.`=COUNTIFS($B$5:$B$10,B5,$C$5:$C$10,C5,$D$5:$D$10,D5)`

If you have two columns only, remove the $D$5:$D$10,D5 part from the formula, i.e.

`=COUNTIFS($B$5:$B$10,B5,$C$5:$C$10,C5)`

Now, copy the formula down. The output number will say how many times each row is repeated.

Regards

-Mahdy

(ExcelDemy Team)