How to Count Duplicate Rows in Excel (4 Methods)

While dealing with a large amount of raw data in MS Excel, we often face duplicate cells, rows, or columns. Sometimes it can be helpful if we can count how many duplicate rows, cells, or columns are there. However, we are going to discuss the methods how to count the duplicate rows in Excel only in this tutorial with suitable examples and proper illustrations.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

4 Methods to Count Duplicate Rows in Excel

Let’s consider a dataset of a fruit shop consisting of Customer’s Name, Product, and Bill.

Now, we will discuss 4 methods to count duplicate rows from any Excel dataset.

1. COUNTIF Function to Count Duplicate Rows in Excel

The COUNTIF function is one of the statistical functions. It counts the number of cells that meet a criterion.

Syntax:

Arguments:
range – It is the part from where the function will count.
criteria – It is the criteria to search from the range.

1.1 COUNTIF With the 1st Occurrence

Step 1:

• We will add a table to show the duplicates count.
• The table shows the duplicates of product and their count.

Step 2:

• Go to Cell C13.
• Type the COUNTIF function.
• In the argument section, we will search B13 in the range B5:B10 and count if found. The formula is:

Step 3:

• Now, press Enter.

Step 4:

• Pull the Fill Handel icon to the last cell.

Finally, we get the results with the 1st occurrence.

Read More: How to Count Duplicates Based on Multiple Criteria in Excel

1.2 COUNTIF Without 1st Occurrence

Step 1:

• Modify the previous formula.
• Subtract 1 from that formula and the formula will look like this:

Step 2:

• Now, press Enter.

Step 3:

• Pull the Fill Handle icon to the last cell.

The third column will show the results without 1st occurrence. Also, one interesting thing is that we can easily say which item is unique. From the result where showing 0, means that item is not duplicated.

Note: Keep in mind that this method cannot identify case-sensitive objects.

Read More: How to Count Occurrences Per Day in Excel (4 Quick Ways)

2. SUM + EXACT Functions to Count Duplicate Rows (Case-Sensitive Approach)

The SUM functions used for summation. The sum objects may be numbers, cell references or range, or the combination of all.

Syntax:

Argument:
number1 – The first number we want to add. The number can be like 4, a cell reference, or a cell range.
number2-255 – This is the second number we want to add. We can add up to 255 numbers.

The EXACT function compares two text strings. And returns TRUE if they are the same, FALSE otherwise. EXACT is case-sensitive. We use EXACT to test text being entered into a document.

Syntax:

Argument:
text1 – The first text string.
text2 – The second text string.

In this section, we will use the combination of the SUM and EXACT functions to count duplicates with case sensitivity.

Step 1:

• First, check the data if there is any case-sensitive data in Column B.
• We see that Joe and joe are two case-sensitive data that exist on our sheet.

Step 2:

• Go to Cell C13 and write the formula.

Step 3:

• Now, press Enter.

Notice that Joe is showing 1 on the count box.

Step 4:

• Pull the Fill Handle icon to the last cell.

Finally, we get the result with case sensitivity. Liz is showing 2 as no case difference exists, Joe is 1 because of case difference.

Formula Breakdown:

=SUM(–EXACT($B$5:$B$10,B13))

• EXACT($B$5:$B$10,B13)
  This function will search for the exact matching of B13 in the range B5:B10. If found will show TRUE otherwise FALSE for each of the cells.
  Output: {FALSE; FALSE; FALSE; FALSE;FALSE;TRUE}
• SUM(–EXACT($B$5:$B$10,B13))
  This will sum the TRUE outputs of the exact function.
  Output: 1

Read More: How to Count Duplicates in Column in Excel (3 Ways)

Similar Readings

• How to Count Duplicates in Two Columns in Excel (8 Methods)
• Count Duplicates in Excel Pivot Table (2 Easy Ways)
• How to Count Duplicate Values in Multiple Columns in Excel (6 Ways)

3. COUNTIFS Function to Count Duplicate Rows

The COUNTIFS Function contains conditions, apply those conditions among the specified range. Then counts how many times those conditions are fulfilled.

Syntax:

Arguments:
criteria_range1 – This is the first range where the conditions will be applied.
criteria1 – This is the condition that will be applied to the first range.
criteria_range2, criteria2, … – The additional ranges and corresponding conditions.

In this section, we will apply the COUNTIFS function that will count duplicate rows.

Step 1:

• We modified the data to apply the COUNTIFS and show the duplicated rows.

Step 2:

• Go to Cell C13.
• Write the COUNTIFS function.
• Complete the formula and the formula will look like as:

Step 3:

• Now press Enter.

We can see that result is 0, as this combination is not duplicated in our sheet.

Step 4:

• Pull the Fill Handle icon to the last cell.

In the case of Liz get 2 because this data is duplicated, and the rest data are unique row-wise.

Read More: VBA to Count Duplicates in Range in Excel (4 Methods)

4. Total Duplicate Rows in Excel

In this section, we will count the duplicate rows in a column.

4.1 Using COUNTIF function

Step 1:

• We add a column named Duplicate in our data to show which data is duplicated or not.

Step 2:

• Now, write the COUNTIF function in Cell E5. The formula is:

Step 3:

• Now, press Enter.

Step 4:

• Drag the Fill Handle icon to Cell E10.

We get Yes for duplicated data and N/A for the unique and 1st occurrences.

Step 5:

• Add a row in the data set named Total Duplicates.

Step 6:

• Apply the COUNTIF function to count the Yes.

Step 7:

• Now, hit Enter and get the result.

Now, we can say that we have two duplicated data on the mentioned column.

4.2 Combination of COUNTIF with Other Functions

The ROWS function returns the number of rows in a reference or array.

Syntax:

Argument:
array – An array, an array formula, or a reference to a range of cells for which we want the number of rows.

In this section, we will combine the COUNTIF function with other functions.

Step 1:

• Add a new row to show the Total Duplicates.

Step 2:

• Write the formula on Cell D11. The formula is:

Step 3:

• Now, hit Enter.

After applying the formula, we get the total duplicates.

Formula Breakdown:

=ROWS($B$5:$B$10)-SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))

• ROWS($B$5:$B$10)
  This will show the number of rows of the range.
  Output: 6

• COUNTIF($B$5:$B$10,$B$5:$B$10)
  This function will search the B5:B10 range and count how many times it was found on that range of cells. It will give a return for each cell.
  Output: {2;1;2;1;2;2}

• IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0)
  It will return 1 if the previous formula return is equal to 1, otherwise 0. It will give a return for each cell.
  Output: {0;1;0;1;0;0}

• SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))
  It will provide the sum of the IF function where gets 1.
  Output: 2

• ROWS($B$5:$B$10)-SUM(IF(COUNTIF($B$5:$B$10,$B$5:$B$10) =1,1,0))
  This will subtract the value of the SUM function from the Rows function.
  Output: 4

Read More: Excel VBA to Count Duplicates in a Column (A Complete Analysis)

Conclusion

In this article, we explained 4 methods to explain how to count duplicate rows in Excel. I hope this will satisfy your needs. Please have a look at our website exceldemy.com and give your suggestions in the comment box.

Related Articles

• How to Count Duplicate Values Only Once in Excel (3 Ways)
• Count Number of Occurrences of Each Value in a Column in Excel
• How to Count Repeated Words in Excel (11 Methods)
• Count the Order of Occurrence of Duplicates in Excel (4 Methods)
• How to Ignore Blanks and Count Duplicates in Excel (3 Ways)