SUMPRODUCT With Multiple Criteria

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is SUMPRODUCT With Multiple Criteria In Excel?

SUMPRODUCT with multiple criteria in Excel helps compare the arrays with numerous criteria.

The format for SUMPRODUCTFormat For SUMPRODUCTThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products.read more with multiple criteria in Excel will remain the same as the SUMPRODUCT formula. The only difference is that it will have multiple criteria for two or more ranges and then add up those products.

For example, consider the below table showing sample with expected and actual sales. Now, assume that we have to find the sample with less sales than the expected sales.

Sumproduct with Multiple criteria Intro

The steps are:

Step 1: Firstly, we should insert the SUMPRODUCT formula in the desired cell.

Step 2: The formula is =SUMPRODUCT((C2:C6<B2:B6)*(A2:A6=”B”))

Sumproduct with Multiple criteria Intro - Step 2.jpg

Likewise, we can use the SUMPRODUCT formula with multiple criteria in Excel.

Key Takeaways

  • SUMPRODUCT with multiple criteria is a method used to obtain results using SUMPRODUCT formula with criterias.
  • We can use this function in place of formulas like SUMIFCOUNTIF, etc.
  • Also, we can use it to create a complex formula that sums up the arrays’ rows and columns.
  • Similarly, users can use SUMPRODUCT function in Excel with logical operators like AND, OR, & both.
  • The wild card characters like an asterisk (*) and question mark (?) are not valid using the SUMPRODUCT formula.

How To Use SUMPRODUCT With Multiple Criteria Excel Function?

  1. We can use it in place of formulas like SUMIFFormulas Like SUMIFThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read moreCOUNTIFCOUNTIFThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more, etc.
  2. We can use it to create a complex formula that sums up the arrays’ rows and columns.
  3. We can use it with logical operatorsLogical OperatorsLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more like AND, OR, & both.

Let us learn this SUMPRODUCT formula with the help of following examples.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Example #1

Let us assume we have a list of company products in the second column. The third column is for the planned count of products to be sold, and the actual sales happened in column fourth. Now, the company wants to determine how many platinum products sold have made fewer sales than the planned count.

Below are the steps of using SUMPRODUCT with multiple criteria in Excel:

  1. Data for the above case is shown below:


    sumproduct in excel example 3.1

  2. In this case, we have two conditions: Firstly, to find the count of the number of products that are less than the planned count. Secondly, that count should be of a platinum product only.


    sumproduct in excel example 3.2

  3. Now, we will use the SUMPRODUCT formula to calculate the count with multiple criteria.

    The final count of sold products that are less than planned, that too product should be platinum is shown below:


    sumproduct in excel example 3.3

Example #2

Let us assume we have a list of company products in the second column, zone in the third column, and fourth for the planned count of products to be sold. The actual sales that happened are in the fifth column. The company wants to determine how many platinum products sold in the north zone have made less than the planned count.

  • Data for the above case is shown below:
Example 4.1
  • In this case, we have two conditions: Firstly, to find the count of the number of products that are less than the planned count. Secondly, that count should be of a platinum product only. Thirdly, they should sell that product in the “North” zone.

Now, we will use the SUMPRODUCT formula in excelSUMPRODUCT Formula In ExcelThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products.read more to calculate the count with multiple criteria.

Example 4.2
  • The final count of the number of sold products which are less than planned, that too product should be platinum which lies in “North” zone is shown below:
Example 4.3

Example #3

Let us assume that student Andy has completed his exams and has a list of expected and obtained marks in various subjects. Column A shows the subject and columns B and C shows the expected and obtained marks.

Sumproduct with Multiple criteria - Example 3.jpg
  • In this case, we have two conditions: Firstly, to find the count of subjects where he has obtained less than the expected marks.

Now, we will use the SUMPRODUCT formula in excel to calculate the count with multiple criteria.

We can see the result as shown in the below image.

Example 3 - Output.jpg

Important Things To Note

  • There should be the same number of rows and columns in all the SUMPRODUCT formulas. Otherwise, it will return an error.
  • The SUMPRODUCT formula treats all the non-numeric values as zero.
  • Without using a double negative sign or multiple the formula with one, the SUMPRODUCT formula will return an error.

Frequently Asked Questions

1. What are some important points to remember while using SUMPRODUCT with multiple criteria?

• While calculating the SUMPRODUCT with multiple criteria in Excel, we have to use the double negative (–) sign or multiply the formula value with a numeric one (1). The double negative sign is technically called the double unary operator.
• The double unary operator covers the “TRUE” and “FALSE” into ones and zeroes, respectively.

2. Explain SUMPRODUCT with multiple criteria with an example.

For example, consider the below table showing sample with expected and actual sales. Now, assume that we have to find the sample with less sales than the expected sales.

FAQ 2

The steps are:

Step 1: Firstly, we should insert the SUMPRODUCT formula in the desired cell.

Step 2: The formula is =SUMPRODUCT((C2:C6<B2:B6)*(A2:A6=”Orange”))

FAQ 2 - Step 2.jpg

Likewise, we can use the SUMPRODUCT formula with multiple criteria in Excel.

3. What is the formula for SUMPRODUCT with multiple criteria?

The formula for format of SUMPRODUCT with single criteria

=sumproduct(–(array1 <Condition> array2)
• =sumproduct((array1 <Condition> array2)*1)


The formula for format of SUMPRODUCT Multiple Criteria

• =sumproduct((array1 <Condition1> array2)*(array3 <condition2>))

This article is a guide to SUMPRODUCT with Multiple Criteria in Excel. Here, we discuss using the SUMPRODUCT function with multiple criteria in Excel, examples, and a downloadable Excel template. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *