How to Count Duplicates in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to Count Duplicates in Excel


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)

Using COUNTIF Function to Count Duplicates in Excel

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.

Using Fill Hanlde Tool to Count Duplicates in Excel

As a result, you can see the Grades with their duplicate count.

Count Duplicates in Excel

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

Counting Duplicates Excluding First Occurrence in Excel

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.

Using SUM and EXACT Function to Count Duplicates in Excel

Steps:

  • First of all, we will type the following formula in cell G5.
=SUM(--EXACT($D$5:$D$13,F5))

Count Duplicates in Excel

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.

Counting Duplicates of Case Censitive Values in Excel

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.

Applying COUNTIFS Function to Count Duplicates in Excel

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.

Counting Duplicate Rows Including First Column


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.

Counting Duplicate Rows Excluding First Occurrence

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","")

Use of IF and COUNTIF Functions to Count Duplicates in Excel

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")

Counting Duplicates in a Column with First Occurrence

  • 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.

Count Duplicates in Excel

Therefore, you can see the duplicate count of the Grade in the Pivot Table.

Use of Pivot Table to Count Duplicates in Excel

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


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.


Related Articles

Asikul Himel

Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo