How to Count Duplicates in Excel (5 Methods)

final result

While working with a large amount of data or compiling multiple worksheets into a bigger one, duplicate values, columns or rows may appear. Sometimes we need to count them to get a clear idea. You can do this easily by using the “COUNTIF” and other functions. In this article, we will learn how to count duplicates in excel using five different methods.

Download Practice Workbook

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

Five Methods to Count Duplicates in Excel

1. How to Count Duplicate Values Including the First Occurrence

In this method, we will learn to count duplicate values including the first occurrences.

Step-1:

The following example includes a dataset where we have the name of different items with their quantity and grades. We have to count duplicates in the “Grade” column

creating table

Step-2:

Create a table anywhere in the worksheet where we will count the duplicates.

creating table

Step-3:

Now, we will count this by using the “COUNTIF” function.

The format of this function is

=COUNTIF(Range, Criteria)

Where,

  • Range: the data range from where we want to count duplicate values
  • Criteria: The specific value which will be counted

Apply the “COUNTIF” function.

Here,

  • The range is $D$4:$D$12.
  • The criterion is F4.

applying formula

Press “Enter”.

result

We have got the duplicate count for “A”. Now apply the same function for all the criteria by selecting and dragging your cursor.

final result

2. How to Count Duplicate Values excluding the First Occurrence

Step-1:

Here we will count duplicate values without considering the first occurrence.

Apply the “COUNTIF” formula. The final formula is,

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

final result

Press “Enter”. The duplicate values are shown without the first occurrence.

creating table

Step-2:

Select the cell and drag the cursor to get all the results.

applyting formula

3. How to Count Case-Sensitive Duplicates in Excel

The “COUNTIF” function in Excel is case-insensitive. So we need to apply different functions to get the case-sensitive duplicates. We will use the combination of the “EXACT” and “SUM” functions in this case.

Step-1:

Consider a table with case-sensitive values like the given one in the picture.

creating table

Step-2:
Apply this formula in the cell where you want to get the duplicate values

=SUM(–EXACT($D$4:$D$12,F4))

Where,

  • Text1 is $D$4:$D$12
  • Text2 is F4

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

applying formula

Press “CTRL + SHIFT + ENTER” to transform the formula as an array formula.

result

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

final result

4. Counting Duplicate Rows in Excel

This method can be done in two ways. Let’s discuss.

i. Including the First Occurrence

Step-1:

In the following example, we have some duplicate rows containing identical information. We will now learn how to count these rows including the first occurrence.

Count duplicate rows

Step-2:

To count the duplicate values we will use the “COUNTIFS” function.

The final form is the function is

=COUNTIFS($B$4:$B$12,B4,$C$4:$C$12,C4,$D$4:$D$12,D4)

Count duplicate rows

Get the result by pressing “Enter”.

Count duplicate rows

Step-3:

Do the same for the rest of the duplicate rows.

Count duplicate rows

ii. Excluding the First Occurrence

Step-1:

To get the duplicate rows without the first occurrence, apply this formula

=COUNTIFS($B$4:$B$12,B4,$C$4:$C$12,C4,$D$4:$D$12,D4)-1

Count duplicate rows without first occurrence

Press “Enter” to get the value.

Count duplicate rows without first occurrence

Step-2:

Apply the same formula by clicking and dragging the mouse.

Count duplicate rows without first occurrence

5. How to Count the Total Number of Duplicates in a Column

Like counting duplicate rows, we can count duplicates in a column in excel. Let’s see how.

i. Including the First Occurrence

Step-1:

Consider a table where we have duplicate values in a column. We have to count those duplicate values.

Count duplicate value in a column

Step-2:

Apply the “IF” and the “COUNTIF” function.

The final form of the formula is

=IF(COUNTIF($B$4:$B$4:$B$12,B4)>1,”DUPLICATE”,””)

Count duplicate value in a column

Press “Enter” and drag the formula to the other cells to get the duplicate values in a column.

Count duplicate value in a column

Step-3:

To count the total duplicates, apply this formula.

Count duplicate value in a column

Press “Enter”. We got the values of the total duplicates.

Count duplicate value in a column

ii. Excluding the First Occurrence

Step-1:

To count duplicates in a column excluding the first occurrence, apply this formula

=IF(COUNTIF($B$4:B4,B4)>1,”YES”,””)

Where we changed the cell references from $B$4:$B$4:$B$12 to $B$4:B4

count duplicate value in a column without first value

Press “Enter”.

count duplicate value in a column without first value

So now we got only the duplicate values excluding the first occurrence.

Step-2:

Now we will count the duplicates.

count duplicate value in a column without first value

Press “Enter”. The result is given in the cell.

count duplicate value in a column without first value

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 five different conditions. Hope this article is useful to you. Share your thoughts in the comment box.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo