# SUMPRODUCT and COUNTIF Functions with Multiple Criteria

Get FREE Advanced Excel Exercises with Solutions!

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,
`=SUMPRODUCT(COUNTIF(E5:E14,">70000"))-SUMPRODUCT(COUNTIF(E5:E14,">95000"))`

Formula Breakdown:

• SUMPRODUCT(COUNTIF(E5:E14,”>70000″)) will count cells greater than 70000.
• SUMPRODUCT(COUNTIF(E5:E14,”>95000″)) will count cells less than 95000.
• So, the above formula will find cells for 70000< cells < 95000.
• 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,
`=SUMPRODUCT(COUNTIF(D5:D14,"Fridge"))`

Formula Breakdown:

• D5:D14 is the range and “Fridge” is the criteria of the COUNTIF function.
• COUNTIF(D5:D14,”Fridge”) is the array of the SUMPRODUCT function.
• 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. ### 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.
`=SUMPRODUCT(COUNTIF(C5:D14,">1 May 2021"))`

Formula Breakdown:

• C5:D14 is the range and “>1 May 2021” is the criteria of the COUNTIF function.
• COUNTIF(C5:D14,”>1 May 2021″) is the array of the SUMPRODUCT function.
• 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. ### 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,
`=SUMPRODUCT(COUNTIF(B5:E14,"C*"))`

Formula Breakdown:

• B5:E14 is the range and “C*” is the criteria of the COUNTIF function.
• COUNTIF(B5:E14,”C*”) is the array of the SUMPRODUCT function.
• 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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  