How to Count Duplicates in Excel

We will use the following dataset of items and their quantities and grades to showcase how you can count duplicate items.

Overview to Count Duplicates


Method 1 – Using the COUNTIF Function to Count Duplicates in Excel (Including First Occurrence)

  • Apply the following formula to count duplicates with the first occurrence in the Grade column.
=COUNTIF($D$5:$D$13,F5)

Count Duplicates with First Occurrence in Excel


Method 2 – Inserting the COUNTIF Function for Counting Duplicates in Excel (Excluding the First Occurrence)

  • Apply the following formula:
=COUNTIF($D$5:$D$13,F5)-1

Count Duplicates with First Occurrence in Excel


Method 3 – Combining SUM and EXACT Functions to Count Case-Sensitive Duplicates

  • Insert the following formula in a new cel:
=SUM(--EXACT($D$5:$D$13,F5))

Counting Case Sensitive Duplicates


Method 4 – Inserting a Pivot Table to Count Duplicates in Excel

  • Select the whole dataset.
  • Go to Insert tab, select Tables, and choose Pivot Tables.

Create Pivot Table to Count Duplicates in Excel

  • In the dialog box, select Existing Worksheet and select the Range for pivot output if you want the pivot in the same worksheet.

Selecting Range for Pivot

  • In the Pivot Table Field from the right pane, put the Grade field in the Row and values sections.

7-Selecting Fields for Pivot

  • You will see the pivot table counting the duplicates in the Grade column.

Counting Duplicates with Pivot Table


How to Count Duplicate Rows in Excel

  • Apply the following formula.
=COUNTIFS($B$5:$B$13,B5,$C$5:$C$13,C5,$D$5:$D$13,D5)

Count Duplicates Rows in Excel


How to Count Duplicate Values with Multiple Criteria in Excel

We will count the duplicates in column Region using multiple conditions from columns Region and Product.

  • Insert the following formula in a new cell.
=COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,D5)

Duplicates Based on Multiple Criteria


How to Remove Duplicates in Excel

  • Select the dataset.
  • Go to the Data tab, choose Data Tools, and select Remove Duplicates.

Removing Duplicates

  • We will get the duplicates removed.

Removed Duplicates


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.

Frequently Asked Questions

What does “Count Duplicates” mean in Excel?

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

Can I count duplicates based on multiple columns in Excel?

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.

Can I automate the process of counting duplicates in Excel using VBA?

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 the Practice Workbook


Count Duplicates in Excel: Knowledge Hub

 


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo