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.


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.

sumproduct countif multiple criteria


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.

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

Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel


Similar Readings


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.

Utilizing SUMPRODUCT and COUNTIF Functions with Multiple Criteria for Dates

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.


Md. Abdur Rahim Rasel

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

Leave a reply

ExcelDemy
Logo