Md. Abdur Rahim Rasel
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Examples to Use SUMPRODUCT and COUNTIF Functions with Multiple Criteria in Excel
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.
Read More: How to Apply COUNTIF Function in Excel with Multiple Criteria
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.
Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel
Similar Readings
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- COUNTIF with Multiple Criteria in Different Columns in Excel
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
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.
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,
=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.
Read More: Excel COUNTIF for Multiple Criteria with Different Column
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.
