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.
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.
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.
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.
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.
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
- Count the Order of Occurrence of Duplicates in Excel
- How to Count Duplicates Based on Multiple Criteria in Excel
- How to Count Occurrences Per Day in Excel
<< Go Back to Count Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
