Prantick Bala
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.
Download Practice Workbook
4 Ways to Count Duplicates in Excel Based on Multiple Criteria
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.
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.
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.
Related Content: Count the Order of Occurrence of Duplicates in Excel (4 Methods)
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.
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.
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.
Read More: How to Count Duplicates in Two Columns in Excel (8 Methods)
Similar Readings:
- Excel VBA to Count Duplicates in a Column (A Complete Analysis)
- How to Ignore Blanks and Count Duplicates in Excel (3 Ways)
- VBA to Count Duplicates in Range in Excel (4 Methods)
- How to Count Repeated Words in Excel (11 Methods)
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.
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.
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.
Related Content: Count Duplicates in Excel Pivot Table (2 Easy Ways)
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&D5It will combine the criteria from cells C5 and D5 in cell F5.
After that,
➤ Press ENTER,
As a result, you will get the combined criteria in cell F5.
➤ Drag cell F5 to the end of your dataset.
So, you’ll get the combined criteria for your entire dataset.
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.
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.
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.
Read More: How to Count Duplicate Values Only Once in Excel (3 Ways)
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.
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,
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.