How to Count Duplicates in Column in Excel (3 Ways)

Count Duplicates in a Column Including the First Occurrence

While working with a large dataset or merging multiple worksheets into one, there is a possibility that you are getting duplicate values or columns into the worksheet. Sometimes we may need to count those duplicate values to get a clear concept about the worksheet. Excel provides some basic functions and formulas by which you can easily count duplicate values in a column. Today, in this article, we will learn how to count duplicates in columns in Excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Count Duplicates in Column (3 Ways)

1.   Using the COUNTIF Function to Count Duplicates in Column

The basic COUNTIF function helps you to find duplicates in columns for two different scenarios. Let’s learn!

i. Count Duplicates in a Column Including the First Occurrence

Step-1:

Consider a situation where we are given a dataset containing columns “Name”, “Salary”, and “Region” of some sales rep. We have to find duplicates in the “Region” column.

Count Duplicates in a Column Including the First Occurrence

Step-2:

Create a new dataset with the columns “Region”, and “Count”. In the Region column, the unique names of the regions are given. We will find their duplicates in the Count column.

Count Duplicates in a Column Including the First Occurrence

Step-3:

In cell G4, apply the COUNTIF function. The generic function is,

=COUNTIF(Range, Criteria)

Insert the values into the function and the final form is,

=COUNTIF($D$4:$D$15,F4)

Where,

  • The range is $D$4:$D$15.
  • The criterion is

Count Duplicates in a Column Including the First Occurrence

Press Enter to get the result.

Count Duplicates in a Column Including the First Occurrence

Step-4:

Now move your mouse cursor to the bottom right corner of the formula cell until you see the fill handle icon (+). Then double click on the icon to get the result for the rest of the cells.

Count Duplicates in a Column Including the First Occurrence

ii. Count Duplicates in a Column Excluding the First Occurrence

Step-1:

In this case, we will ignore the first appearance and the count rest of the duplicates in columns. In cell G4 apply this formula,

=COUNTIF($D$4:$D$15,F4)-1

Here using this formula we will exclude the first appeared duplicate.

Count Duplicates in a Column Excluding the First Occurrence

Press Enter to get the result.

Count Duplicates in a Column Excluding the First Occurrence

Step-2:

So we have got the number of duplicates in columns ignoring the first occurrence. Now do the same for the rest of the cells.

Count Duplicates in a Column Excluding the First Occurrence

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

2. Using the SUM with the EXACT Function to Count Case-Sensitive Duplicates in a Column

The COUNTIF function in Excel is case-insensitive. So we will use the combination of the EXACT and the SUM functions for case-sensitive duplicates in columns.

Step-1:

In the given example the column “Salary Grade” contains some case-sensitive duplicates. We have to count those duplicates in columns and find results in the Count column.

Using the SUM with the EXACT Function to Count Duplicates in a Column

Step-2:

Now we will use the SUM with the EXACT formula to count duplicates in columns. Insert the values and the final formula is,

=SUM(–EXACT($D$4:$D$15,G4))

Where,

  • Text1 is $D$4:$D$15
  • Text2 is G4

To convert the values to an array of 0 and 1’s, the unary operator (–) is used.

Using the SUM with the EXACT Function to Count Duplicates in a Column

Since this formula is an array formula, press “CTRL + SHIFT + ENTER” to apply the formula.

Using the SUM with the EXACT Function to Count Duplicates in a Column

Step-3:

Our case-sensitive duplicate count is here. Now we will get the rest of the duplicate counts.

Using the SUM with the EXACT Function to Count Duplicates in a Column

3. Using the IF with COUNTIF Function to Count Total Duplicates in Columns

Step-1:

In this case, we will count duplicates in the Region column under the Duplicate column and find out the total duplicates in this column.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Step-2:

To do this, apply the If with the COUNTIFS formula. After inputting the values, the final formula is,

=IF(COUNTIF($D$4:$D$15,D4)>1,”DUPLICATE”,””)

Where if the COUNTIF function counts values more than once, the IF function argument will show “DUPLICATE”.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Press Enter to get the result.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Step-3:

Do the same for the rest of the column cells.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Step-4:
To count the total duplicates, apply this formula.

=COUNTIF(E4:E15,E4)

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Press Enter to get the total duplicates in columns.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Step-5:

Now if we want to count the duplicates in a column excluding the first occurrence, input this formula

=IF(COUNTIF($D$4:D4,D4)>1,”YES”,””)

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

Now press Enter to get the result.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

And get the total count by using the same formula. And our job is done.

Using the IF with COUNTIF Function to Count Total Duplicate Numbers in a Column

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

⏩ Use the unary operator (–) to transform the result of the EXACT function to an array of 0 and 1’s.

Conclusion

Today we discussed how to count duplicates in columns. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo