How to Count Duplicates in a Column in Excel – 4 Easy Methods

This is the sample dataset.

excel count duplicates in column Sample Dataset


Method 1. Using the COUNTIF Function to Count Duplicates in a Column in Excel

1.1 Counting Duplicates Including the First Occurrence

Steps:

  • Select C13 and enter the following formula.

=COUNTIF(C5:C11,C6)

Insert COUNTIF Function in Excel to Count Duplicates in Column Including First Occurrence

  • Press Enter to count the number of duplicates.


1.2 Counting Duplicates Excluding First Occurrence

Steps:

  • Select C13 and enter the following formula.

=COUNTIF($C$5:$C$11,C6)-1

Insert COUNTIF Function in Excel to Count Duplicates in Column Excluding First Occurrence

  • Press Enter to count the number of duplicates.

Read More: How to Count Duplicates in Two Columns in Excel


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

The dataset was slightly changed.

excel count duplicates in column

Steps:

  • Click D13 and enter the formula below.

=SUM(--EXACT($D$5:$D$11,D5))

Combine SUM and EXACT Functions for Counting Case-Sensitive Duplicates in Columns

Formula Breakdown:

  • In the Exact function, Text1 is $D$5:$D$11 and Text2 is D5(A). It searches Text2 from the Text1 range.
  • To convert the values to an array of 0 and 1’s, the unary operator () is used.
  • The SUM function provides the sum of the exact match of D5.
  • Press Enter button to see the output.

Read More: How to Count Duplicate Values in Multiple Columns in Excel


Method 3 – Finding Total Duplicates in a Column by Joining IF and COUNTIFS in Excel

The sample dataset was slightly changed.

excel count duplicates in column

Steps:

  • Click D5 and enter the formula below.

=IF(COUNTIFS($C$4:$C$11,C5)>1,"DUPLICATE","")

Find Total Duplicates in Column by Joining IF and COUNTIFS in Excel

Formula Breakdown:

  • The COUNTIFS function counts the text in C5 from the  $D$5:$D$11 range.
  • The IF function checks the output with the given condition.
  • If the COUNTIFS function counts values more than once, the IF function argument will show “DUPLICATE”. If it doesn’t, it returns blank.
  • Press Enter and drag down the AutoFill tool.

  • Select D13 and enter the formula below.

=COUNTIF(D5:D11,D6)

  • Press Enter to find the total count of duplicates.

Read More: How to Count Duplicate Rows in Excel


Method 4 – Counting Duplicates in a Column with an Excel Pivot Table

Steps:

  • Select the dataset: B4:C11.
  • Go to the Insert tab, choose PivotTable and select From Table/Range.

Count Duplicates in Column with Excel Pivot Table

  • A PivotTable from table or range dialog box will appear.
  • Check Existing Worksheet and select F4 in the Location box.
  • Click OK.

  • The PivotTable Fields dialog box will appear.
  • Enter Region in Rows and Values.

  • The Region duplicate count will be displayed in the Pivot Table.

Read More: Count Number of Occurrences of Each Value in a Column in Excel


Download Practice Workbook

Download the workbook from the link below.


Related Articles


<< Go Back to Count Duplicates in Excel | 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