How to Count Duplicate Values in Excel?

Are you trying to count duplicate values in Excel? Sometimes we compile multiple worksheets into in single large excel sheet. This integrated single worksheet may consist of duplicate values.

You may require to count the duplicate values in an excel sheet in order to get a clear view of the large data. The Excel formula COUNTIF() will play a most important role in counting the duplicate in the excel sheet. We have briefly discussed this COUNTIF() function in our previous content.

In this context, we will discuss a few methods to count duplicate values in excel.

1. Count Duplicates
   • with 1^st occurrence
   • without 1^st occurrence
2. Count Case-Sensitive Duplicates in Excel
3. Count Duplicate Rows in Excel

Steps Count Duplicate Values in Excel:

Count Duplicates

Count Duplicates With 1^st Occurrence

In the example below we have data in two columns one column has fruit items and the other has the quantity of each. With the COUNTIF() function implemented in the E column, we are able to count the frequency of duplicate fruit items in column B.

Count Duplicates Without 1^st Occurrence

To count the frequency of duplicates excluding the first occurrence we just have reduced the count by one:

You can notice the difference between the count of duplicates with and without the first occurrence.

Count Case-Sensitive Duplicates in Excel

The COUNTIF function that we have used till now is case-insensitive. To count case-sensitive duplicates we can use the two formulas of Excel in collaboration. The two formulae that we will use are:

1. EXACT: This formula returns TRUE if both the strings provided to it are the same and False if the strings are not the same. EXACT is case-sensitive which means even if the provided string is the same but one is in upper case and the other in lower case then it will return FALSE declaring the two strings are not the same.
2. SUM: This formula returns the total count.

Note: To apply the formula as an array formula accurately press Ctrl + Shift + Enter while applying the formula to each cell.

Count Duplicate Rows in Excel

To count the number of duplicate rows in the excel sheet we will be using COUNTIFS function as you can see in the image below:

To exclude the first occurrence of a row we just have to reduce the result by one.

This is how you can count the occurrence of duplicate values in Excel.