How to Count Duplicates Based on Multiple Criteria in Excel

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


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.

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: 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.

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 (8 Methods)


Similar Readings:


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: 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&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 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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

1 Comment
  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,

Leave a reply

ExcelDemy
Logo