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.
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
Get FREE Advanced Excel Exercises with Solutions!
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 ROWS function 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 COUNTIFS function. 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)