How to Count Duplicates Based on Multiple Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Excel, you may have duplicate data in the same columns. These data may be a part of a unique set when you consider all the data along a row. To be duplicated, all the data along a row don’t need to be duplicated. It depends on the criteria on which you are comparing the data. For example, you want to find out the number of product sellers in a particular region. Each product in each region can be considered a unique value and repetition of the same product in the same region can be considered as duplicates. In this case, you may need to consider multiple criteria to count duplicates in Excel. In this article, I’ll show you 4 ways to count duplicates in Excel based on multiple criteria.

Suppose, you have the following datasets. You want to find out the number of product sellers in a particular region. That’s why you need to find out the duplicates based on region and product type.

dataset


How to Count Duplicates Based on Multiple Criteria in Excel: 4 Ways

1. SUMPRODUCT Function to Count Duplicates Based on Multiple Criteria

In this method, I’ll show you how you can find out the duplicates based on multiple criteria using the SUMPRODUCT function. Let’s start the procedure.

➤ Type the following formula in cell F5,

=SUMPRODUCT(--(C5&D5=$C$5:$C$16&$D$5:$D$16))

Here, the formula will return 1 for each unique set of regions and products, and will give the number of occurrences for duplicate sets of region and product.

sumproduct

After that,

➤ Press ENTER,

As a result, you will get 1. That means there is only one car seller in New York. There are no duplicates of this in the given dataset.

count duplicates in excel multiple criteria

Now,

➤ Drag cell F5 to the end of your dataset.

As a result, you will get the count of the duplicates in your dataset based on multiple criteria which are sales region and product type.

count duplicates in excel multiple criteria

Related Content: How to Count Duplicates in Column in Excel


2. COUNTIFS Function to Count Duplicates Based on Criteria

You can also count duplicates based on multiple criteria using the COUNTIFS function.

➤ Type the following formula in cell F5,

=COUNTIFS($C$5:$C$16,C5,$D$5:$D$16,D5)

Here, the formula will return 1 for each unique set of regions and products, and will give the number of occurrences for duplicate sets of region and product.

countifs

After that,

➤ Press ENTER,

As a result, you will get 1. That means there is only one car seller in New York. There are no duplicates of this in the given dataset.

count duplicates in excel multiple criteria

Now,

➤ Drag cell F5 to the end of your dataset.

As a result, you will get the count of the duplicates in your dataset based on multiple criteria which are sales region and product type.

count duplicates in excel multiple criteria

Read More: How to Count Duplicates in Two Columns in Excel


3. Count Duplicates Based on Multiple Criteria Using SUM and EXACT Functions

Using the SUM function and the EXACT function together also allows you to count duplicates based on multiple criteria. The EXACT function is case-sensitive. So, it only returns true when the compared texts are exactly the same. First,

➤ Type the following formula in cell F5,

=SUM(--(EXACT($C$5:$C$16&$D$5:$D$16,C5&D5)))

Here, the EXACT function gives TRUE if it finds an exact match of product and region among the range. The double dash in the formula (—) converts the TRUE into 1. Finally, the SUM function adds up all the ones. As a result, you will get the counts of duplicates.

sum

After that,

➤ Press ENTER,

As a result, you will get 1. That means there is only one car seller in New York. There are no duplicates of this in the given dataset.

exact

Now,

➤ Drag cell F5 to the end of your dataset.

As a result, you will get the count of the duplicates in your dataset based on multiple criteria which are sales region and product type.

count duplicates in excel multiple criteria

Related Content: How to Count Duplicate Values in Multiple Columns in Excel


4. Count Duplicates by Combining Criteria Altogether

You can also count the duplicates by combining the criteria cells though it is not convenient as you need one extra column which holds the combined criteria. First,

➤ Type the following formula in cell F5,

=C5&D5

It will combine the criteria from cells C5 and D5 in cell F5.

countif

After that,

➤ Press ENTER,

As a result, you will get the combined criteria in cell F5.

combined

➤ Drag cell F5 to the end of your dataset.

So, you’ll get the combined criteria for your entire dataset.

count duplicates in excel multiple criteria

Now, you can use the COUNTIF function to count the duplicates.

➤ Type the following formula in cell G5,

=COUNTIF($F$5:$F$16,F5)

The formula will return 1 for unique values and the number of occurrences for duplicate values.

count duplicates in excel multiple criteria

After that,

➤ Press ENTER,

As a result, you will get 1. That means there is only one car seller in New York. There are no duplicates of this in the given dataset.

count duplicates in excel multiple criteria

Now,

➤ Drag the C5 cell to the end of your dataset.

As a result, you will get the count of the duplicates in your dataset based on multiple criteria which are sales region and product type.

count duplicates in excel multiple criteria

Read More: How to Count Duplicate Rows in Excel


Download Practice Workbook


Conclusion

By following any of the 4 methods described in this article you will be able to count duplicates in Excel based on multiple criteria. The last method, in which you need to combine the criteria cells is not convenient for a large dataset. If you have case-sensitive data in your dataset the third method using the SUM and the EXACT functions should be used. If you have any confusion about any of the methods please feel free to leave a comment.


Related Articles


<< Go Back to Count Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. Hi Prantick,
    Would you know which of the 3 first methods takes less memory?
    The 4th is already discarded because it uses 2 columns instead of one.
    Thank you very much,

    • Reply Avatar photo
      Rubayed Razib Suprov Aug 21, 2022 at 1:10 PM

      Thanks for your question, Jorge F. The allocation of memory mostly depends upon the dataset in which you are working on, and how they are interacting to other functions. For example, if you use a nested function, that function will cost more memory. There are some functions out there that consume more memory compared to other functions. Among the methods described above, the SUMPRODUCT function has more memory allocation compared to other functions. The other two methods would consume more or less the same amount of memory.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo