This article will discuss various ways to count duplicates in Excel. In our methods, we will utilize different features and functions of Excel. While we work with datasets in Excel, sometimes we encounter duplicates. We may need to count the duplicates.
In addition, we will show the way to count duplicates with multiple criteria, the procedure to remove duplicates, etc.
1. Using COUNTIF Function to Count Duplicates in Excel (Including First Occurrence)
In the first method, let’s hover over the procedure to use the COUNTIF function to count duplicates, including the first occurrence.
- Just apply the following formula to count duplicates with the first occurrence in the Grade column.
=COUNTIF($D$5:$D$13,F5)- Finally, use the Fill Handle to copy the formula for the cells below.
2. Inserting COUNTIF Function for Counting Duplicates in Excel (Excluding the First Occurrence)
We will count duplicates like the previous method except for this time, excluding the first occurrence.
- Similarly, apply the following formula to count duplicates, except this time without the first occurrence.
=COUNTIF($D$5:$D$13,F5)-1
3. Combining SUM and EXACT Functions to Count Case-Sensitive Duplicates
We haven’t considered the case’s sensitivity so far. Let’s use the SUM and the EXACT functions to count case-sensitive duplicates.
- Simply insert the following formula in a new cell to count case-sensitive duplicates.
=SUM(--EXACT($D$5:$D$13,F5))
4. Inserting Pivot Table to Count Duplicates in Excel
We can also insert the Pivot table to count duplicates. We will show you the way.
- Firstly, select the whole dataset.
- Then, go to Insert tab > Tables > Pivot Tables.
- Further, in the coming dialog box, select Existing Worksheet and select the Range for pivot output if you want the pivot in the same worksheet.
- Lastly, in the Pivot Table Field from the right pane, take the Grade field in the Row and values sections.
- Finally, you will see the pivot counting the duplicates in the Grade column.
How to Count Duplicate Rows in Excel
Sometimes, a whole row can be a duplicate. Let’s see how to count them.
- Simply, apply the following formula.
=COUNTIFS($B$5:$B$13,B5,$C$5:$C$13,C5,$D$5:$D$13,D5)
How to Count Duplicate Values with Multiple Criteria in Excel
Now, we will show the procedures to count duplicates if they satisfy multiple criteria. For demonstration in our dataset, we will count the duplicates in column Region using multiple conditions from columns Region and Product.
- Just insert the following formula in a new cell.
=COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,D5)
How to Remove Duplicates in Excel
Finally, let’s see a simple way to remove duplicates from the dataset.
- First, select the dataset.
- Then, go to the Data tab > Data Tools > Remove Duplicates.
- Finally, we will see the duplicates removed.
Things to Remember
- Always use the “Absolute Cell Reference ($)” to “Block” the range
- Also, while counting the case-sensitive duplicates, make sure to apply the formula as an “Array Formula” by Pressing “CTRL+SHIFT+ENTER”
- In addition, use the unary operator (- -) to transform the result of the “EXACT” function to an array of 0 and 1’s.
Frequently Asked Questions
1. What does “Count Duplicates” mean in Excel?
Ans: “Count Duplicates” in Excel refers to the process of determining the number of occurrences or instances of duplicate values or entries within a column or range of cells in a worksheet.
2. Can I count duplicates based on multiple columns in Excel?
Ans: Yes, you can count duplicates based on multiple columns in Excel by combining criteria using formulas or PivotTables. For formulas, you can use the COUNTIFS function, which allows you to specify multiple conditions to count duplicates across different columns simultaneously.
3. Can I automate the process of counting duplicates in Excel using VBA?
Ans: Yes, you can automate the process of counting duplicates in Excel using VBA (Visual Basic for Applications). By writing a VBA macro, you can define custom logic to scan columns or ranges, count duplicates, and display or store the results in the desired format. This allows you to automate the duplicate counting process and apply it to multiple worksheets or workbooks efficiently.
Download Practice Workbook
You can download the practice workbook here and exercise.
Conclusion
Counting duplicates may come in handy sometimes while working in Excel. In this article, we have shown different methods to count duplicates in Excel. In addition, we have shown the procedures to count duplicates with multiple criteria, the method to remove duplicates, etc. I hope this article will help you count duplicates. Please leave comments if you have any.
