# How to Count Duplicates in Excel (5 Methods) 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.

## 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 Step-2:

Create a table anywhere in the worksheet where we will count the duplicates. 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. Press “Enter”. We have got the duplicate count for “A”. Now apply the same function for all the criteria by selecting and dragging your cursor. ### 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 Press “Enter”. The duplicate values are shown without the first occurrence. Step-2:

Select the cell and drag the cursor to get all the results. ### 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. 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. Press “CTRL + SHIFT + ENTER” to transform the formula as an array formula. Our case-sensitive duplicate count is here. Now we will get the rest of the duplicate counts. ### 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. 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) Get the result by pressing “Enter”. Step-3:

Do the same for the rest of the 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 Press “Enter” to get the value. Step-2:

Apply the same formula by clicking and dragging the mouse. ### 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. 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”,””) Press “Enter” and drag the formula to the other cells to get the duplicate values in a column. Step-3:

To count the total duplicates, apply this formula. Press “Enter”. We got the values of the total duplicates. #### 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 Press “Enter”. So now we got only the duplicate values excluding the first occurrence.

Step-2:

Now we will count the duplicates. Press “Enter”. The result is given in the cell. ## 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.  