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

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

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

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.

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

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 Rows in Excel

## 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 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

1. Hi Prantick,
Would you know which of the 3 first methods takes less memory?