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

This is the 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)

• 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

• Press Enter to count the number of duplicates.

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

The dataset was slightly changed.

Steps:

• Click D13 and enter the formula below.

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

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.

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

The sample dataset was slightly changed.

Steps:

• Click D5 and enter the formula below.

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

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.

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

## Related Articles

<< Go Back to Count Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF