SUMPRODUCT and COUNTIF Functions with Multiple Criteria

The SUMPRODUCT and COUNTIF functions in Excel count the number of cells within a range that meet a given condition. You cannot apply multiple conditions with these functions directly. However, there are ways to use this Excel function for more than one criterion using some tricks. In this article, we will discuss how you can apply the SUMPRODUCT and COUNTIF functions with multiple criteria in Excel effectively with appropriate illustrations.

How to Use SUMPRODUCT and COUNTIF Functions with Multiple Criteria in Excel: 4 Suitable Examples

Let us have a data set like this. We have the record of the product, sales representatives’ names, and their ID, and revenue earned by those sales representatives are given in columns D, B, C, and E respectively. To do that, we will apply the SUMPRODUCT and COUNTIF functions with multiple criteria. Here’s an overview of the dataset for today’s task.

1. Combine SUMPRODUCT and COUNTIF Functions to Count Cells Between Numbers

Excel users are occasionally required to count the number of cells where the value is between two specified values. In the example below, I want to know the outcome of the revenue earned by the sales representatives between $70000 and $95000 using the SUMPRODUCT and COUNTIF functions. Let’s follow the instructions below to learn!

Steps:

• First of all, select cell E16 and write down the below SUMPRODUCT and COUNTIF functions in that cell. The functions are,

• Hence, simply press Enter on your keyboard. As a result, you will get the revenue earned by the sales representatives between $70000 and $95000 which is the return of the SUMPRODUCT and COUNTIF functions. The return is 6.

2. Applying SUMPRODUCT and COUNTIF Functions with Multiple Criteria for Text in Same Column

For instance, I have the below data that includes various products, and I’d like to figure out how many Fridges are populated in a single column. Let’s follow the instructions below to learn!

Steps:

• Firstly, insert the SUMPRODUCT and COUNTIF functions in cell D16. The functions are given below,

• Further, simply press Enter on your keyboard. As a result, you will be able to count the total number of the Fridge which is the return of the SUMPRODUCT and COUNTIF functions. The return is 3.

Similar Readings

• How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
• COUNTIF with Multiple Criteria in Different Columns in Excel

3. Utilizing SUMPRODUCT and COUNTIF Functions with Multiple Criteria for Dates

Now, we will count the cells whose product was ordered after May 1, 2021. To do that, we will apply the SUMPRODUCT and COUNTIF functions in multiple columns. Let’s follow the instructions below to learn!

Steps:

• Apply the formula with the following functions in cell D16.

• Hence, simply press Enter on your keyboard. As a result, you will be able to count the cells whose product was ordered after 1 May 2021 which is the return of the SUMPRODUCT and COUNTIF functions. The return is 5.

Read More: How to Use COUNTIF Between Two Dates and Matching Criteria in Excel

4. Wildcard Operator with Multiple Criteria in Multiple Columns

Last but not least, we will apply the wildcard operator in the SUMPRODUCT and COUNTIF functions to count the specific cells. We have a total of 3 wildcard characters that are used in Excel.

Asterisk (*) – It searches any number of characters after a text.

Question Mark (?) – This question mark is used to replace a single character.

Tilde (~) – It can nullify the impact of the above two characters.

We will count the sales representatives and products whose names start with the C alphabet. Let’s follow the instructions below to learn!

Steps:

• First of all, select cell E16 and write down the below SUMPRODUCT and COUNTIF functions in that cell. The functions are,

• After that, simply press Enter on your keyboard. Hence, you will be able to count the sales representatives and products whose names start with the C alphabet which is the output of the SUMPRODUCT and COUNTIF functions. The output is 6.

Bottom Line

👉 #N/A! the error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! the error happens when a value is divided by zero(0) or the cell reference is blank.

Download Practice Workbook

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

Conclusion

I hope all of the suitable methods mentioned above to use SUMPRODUCT and COUNTIF functions with multiple criteria will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.