SUMPRODUCT With Multiple Criteria

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

What Is SUMPRODUCT With Multiple Criteria In Excel?

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

The format for SUMPRODUCT 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.

  • 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 SUMIF, COUNTIF, 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 operators like AND, OR, & both.

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

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 excel 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: -

  • SUMIF With VLOOKUP
  • SUMIF Between Two Dates in Excel
  • VLOOKUP with SUM