Asikul Himel
While working with a large amount of data or compiling multiple worksheets into a bigger one, duplicate values, columns or rows may appear. Sometimes we need to count them to get a clear idea. You can do this easily by using the COUNTIF function and other functions. In this article, we will learn how to count duplicates in excel using six different methods.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
6 Methods to Count Duplicates in Excel
The following dataset has the Items, Qty, and Grade Columns. Here, you can easily notice that the dataset has duplicate values. Furthermore, using this dataset, we will count duplicates in Excel. Here, we used Excel 365. You can use any available Excel version.
1. Counting Duplicates in Excel Including First Occurrence
In this method, we will learn to count duplicate values including the first occurrences.
Here, the following dataset has the name of different Items with their Qty and Grade. We have to count duplicates in the Grade column.
Steps:
- First of all, we will type the following formula in cell G5.
=COUNTIF($D$5:$D$13,F5)
Formula Breakdown
- COUNTIF($D$5:$D$13,F5) → the COUNTIF function counts the number of cells that meet the given criteria.
- $D$5:$D$13 → is the range.
- F5 → is the criteria.
- COUNTIF($D$5:$D$13,F5) → becomes
- Output: 3
- Explanation: here, 3 indicates the number of times grades A was found.
- After that, press ENTER.
Hence, you can see the result in cell G5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
As a result, you can see the Grades with their duplicate count.
Read More: Count the Order of Occurrence of Duplicates in Excel (4 Methods)
2. Calculating Number of Duplicates Excluding First Occurrence
Here we will count duplicate values without considering the first occurrence.
Steps:
- In the beginning, we will type the following formula in cell G5.
=COUNTIF($D$5:$D$13,F5)-1
Here, COUNTIF($D$5:$D$13,F5)-1 subtracts 1 from the result returned from the COUNTIF function. As a result, the overall number of duplicates becomes 1 less than the total number of duplicates. Thus, the first occurrence becomes excluded from the total number of duplicates.
- After that, press ENTER.
Therefore, you can see the result in cell G5.
- Moreover, we will drag down the formula with the Fill Handle tool.
Therefore, you can see the number of duplicates of Grades excluding the first occurrence.
Read More: Count Number of Occurrences of Each Value in a Column in Excel
3. Finding Number of Case-Sensitive Duplicates in Excel
In the following dataset, you can see that in the Grade column, we have case-sensitive duplicates. The COUNTIF function in Excel is case-insensitive. So we need to apply different functions to get the case-sensitive duplicates. We will use the combination of the EXACT and SUM functions in this case.
Steps:
- First of all, we will type the following formula in cell G5.
=SUM(--EXACT($D$5:$D$13,F5))
Formula Breakdown
- EXACT($D$5:$D$13,F5) → the EXACT function compares 2 text strings and returns True if they are exactly similar.
- Output: here, the Exact function returns 2 TRUE for Grade A since Grade A has 2 duplicates.
- SUM(–EXACT($D$5:$D$13,F5)) → the SUM function sums up the 2 Trues and gives a numerical value.
- Output: 2
- Explanation: Here, 2 indicates the number of duplicate counts for Grade A.
- If you are using Excel 365, press ENTER. As a result, you can see the result in cell G5.
Note: Since this is an array formula, press CTRL+SHIFT+ENTER if you do not have Excel 365.
- Moreover, we will drag down the formula with a Fill Handle tool.
Hence, you can see the case-sensitive duplicate counts.
Read More: How to Count Duplicates Based on Multiple Criteria in Excel
4. Counting Duplicate Rows in Excel
Here, we will count duplicate rows in Excel. This method can be done in two ways. Let’s discuss this.
4.1. Including First Occurrence
In the following example, we have some duplicate rows containing identical information. We will now learn how to count these rows including the first occurrence.
Steps:
- First of all, we will type the following formula in cell G5.
=COUNTIFS($B$5:$B$13,B5,$C$5:$C$13,C5,$D$5:$D$13,D5)
Formula Breakdown
- COUNTIFS($B$5:$B$13,B5,$C$5:$C$13,C5,$D$5:$D$13,D5)—-> the COUNTIFS function applies criteria to a range of cells and counts the number of times the criteria met.
- Output: 3
- Explanation: here, 3 indicates the number of times the item Apple is found.
- Afterward, press ENTER.
Then, you will see the result in cell G5.
Moreover, we will drag down the formula with the Fill Handle tool.
Therefore, you can see the duplicate row count.
4.2. Excluding First Occurrence
Here, we will find out the duplicate rows excluding the first occurrence.
Steps:
In the beginning, we will type the following formula in cell G5.
=COUNTIF($D$5:$D$13,F5)-1
Here, COUNTIF($D$5:$D$13,F5)-1 subtracts 1 from the result returned from the COUNTIF function. As a result, the overall number of duplicates becomes 1 less than the total number of duplicates. Thus, the first occurrence becomes excluded from the total number of duplicates.
- Afterward, press ENTER.
Then, you will see the result in cell G5.
- Moreover, we will drag down the formula with the Fill Handle tool.
Hence, you can see the result in cells G5:G8.
Read More: How to Count Occurrences Per Day in Excel (4 Quick Ways)
5. Total Duplicate Values in a Column
Like counting duplicate rows, we can count duplicates in a column in excel. Let’s see how.
5.1. Including First Occurrence
Consider a table where we have duplicate values in a column. We have to count those duplicate values.
Steps:
First of all, to find out the duplicate Items, we will type the following formula in cell C5.
=IF(COUNTIF($B$5:$B$5:$B$13,B5)>1,"DUPLICATE","")
Formula Breakdown
- COUNTIF($B$5:$B$5:$B$13,B5) → the COUNTIF function counts the number of cells that meet the given criteria.
- $B$5:$B$5:$B$13 → is the range.
- B5 → is the criteria.
- COUNTIF($B$5:$B$5:$B$13,B5) → becomes
- Output: 1
- IF(COUNTIF($B$5:$B$5:$B$13,B5)>1,”DUPLICATE”,””) → the IF function makes a logical comparison between a value and the value we expect.
- Output: Blank cell.
- Explanation: Since the logical comparison of the IF function is FALSE, it returns a Blank cell.
- After that, press ENTER.
Therefore, you can see that cell C5 is a blank cell.
- Furthermore, we will drag down the formula with a Fill Handle tool to cell C13.
Therefore, you can see the DUPLICATE in the Duplicate column.
Next, we will find out the Duplicates in the column.
- After that, we will type the following formula in cell C14.
=COUNTIF($C$5:$C$13,"DUPLICATE")
- Moreover, press ENTER.
Therefore, you can see the duplicate count in a column in cell C14.
5.2. Excluding First Occurrence
Here, we will count duplicates in a column excluding the first occurrence.
Steps:
- First of all, we will type the following formula in cell C5.
=IF(COUNTIF($B$5:B5,B5)>1,"YES","")
- After that, press ENTER.
Therefore, you can see that cell C5 is a blank cell.
- Furthermore, we will drag down the formula with a Fill Handle tool to cell C13.
Then, you can see duplicate YES in the Duplicate column.
In addition, we will calculate the duplicate in the column excluding the first occurrence.
- Moreover, we will type the following formula in cell C14.
=COUNTIF(C5:C13,"YES")-1
Here, COUNTIF(C5:C13,”YES”)-1 subtracts 1 from the result returned from the COUNTIF function. As a result, the overall number of duplicates becomes 1 less than the total number of duplicates. Thus, the first occurrence becomes excluded from the total number of duplicates.
- Afterward, press ENTER.
Then, you can see the number of duplicates in a column excluding the first occurrence in cell C14.
Read More: How to Count Duplicates in Two Columns in Excel (8 Methods)
6. Inserting Pivot Table in Excel to Count Duplicates
In this method, we will use a Pivot Table to count duplicates in Excel. This method is a handy and easy way to do the task.
Steps:
- In the beginning, we will select the dataset by selecting cells B4:D13.
- After that, go to the Insert tab.
- Then, from the PivotTable group >> we will select From Table/Range.
At this point, a PivotTable from table or range dialog box will appear.
- Then, we will select the Existing Worksheet.
- Moreover, we will select cell F4 in the Location box.
- Furthermore, click OK.
Then, a PivotTable Fields dialog box will appear at the right end of the Excel sheet.
- Afterward, we will drag Grade in the Rows and Values group.
Therefore, you can see the duplicate count of the Grade in the Pivot Table.
Read More: How to Count Duplicates with Pivot Table in Excel
Things to Remember
- Always use the “Absolute Cell Reference ($)” to “Block” the range
- While counting the case-sensitive duplicates, make sure to apply the formula as an “Array Formula” by Pressing “CTRL+SHIFT+ENTER”
- Use the unary operator (- -) to transform the result of the “EXACT” function to an array of 0 and 1’s.
Practice Section
You can download the above Excel file to practice the explained method.
Count Duplicates in Excel: Knowledge Hub
- Count Repeated Words
- Count Duplicate Values Only Once
- Ignore Blanks and Count Duplicates
- Count Duplicates Based on Multiple Criteria
- Count Duplicate Rows
- Count Duplicates in Column
- VBA to Count Duplicates in a Column
- Count Duplicates in Two Columns
- Count Duplicate Values in Multiple Columns
- VBA to Count Duplicates in Range
- Count the Order of Occurrence of Duplicates
- Count Number of Occurrences of Each Value in a Column
- Count Occurrences Per Day
Conclusion
Here, we tried to show you 6 methods to count duplicates in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
