SUMPRODUCT with Multiple Criteria

SUMPRODUCT with Multiple Criteria in Excel

SUMPRODUCT with Multiple Criteria in excel helps in comparing the different arrays with multiple criteria.

  1. The format for SUMPRODUCTFormat For SUMPRODUCTThe SUMPRODUCT function in excel is used to multiply the array or range and provide the sum of a product. It is to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of corresponding numbers product in one or more ranges/arrays.read more with Multiple Criteria in excel will remain the same as of Sum product formula. The only difference is that it will have multiple criteria for to multiple two or more ranges & then adding up those products.
  2. In addition, while calculating the SUMPRODUCT with multiple criteria in excel, we have to use The double negative (–) sign or multiply the formula value with numeric one (1). The double negative sign is technically called the double unary operator.
  3. Double Unary operator covers the ‘TRUE’ and ‘FALSE’ into ones and zeroes, respectively.

Format of SUMPRODUCT with single criteria

=sumproduct(–(array1 <Condition> array2)

OR

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

Format of SUMPRODUCT Multiple Criteria

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

How to use SUMPRODUCT with Multiple Criteria in Excel?

  1. It can be used in place of formulas like SUMIFFormulas Like SUMIFThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range).read more, COUNTIF, Etc.
  2. It can be used to create a complex formula that sums up both the rows & columns of all the arrays.
  3. It can be used with logical operatorsLogical OperatorsIn Excel, logical operators, also known as comparison operators, are used to compare two or more values. Depending on whether the condition matching is true or false, these operators return the output.read more like AND, OR & with both as well.

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

Example #1

Let us assume we have a list of products of a company in the 2nd column, 3rd column is for the planned count of products to be sold, and the actual sales happened in column 4th. Now, the company wants to find out 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, which 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 sum-product formula to calculate the count with multiple criteria.

    The final count of a number of sold products which 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 products of a company in the 2nd column, Zone is in the 3rd column, 4th is for the planned count of products to be sold, and the actual sales happened in column 5th. Now, the company wants to find out how many Platinum products sold in North Zone have made less count 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, which are less than the planned count, secondly, that count should be of a Platinum product only & thirdly that product should be sold in North Zone.

Now, we will use the SUMPRODUCT formula in excelSUMPRODUCT Formula In ExcelThe SUMPRODUCT function in excel is used to multiply the array or range and provide the sum of a product. It is to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of corresponding numbers product in one or more ranges/arrays.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

Things to Remember

  • Wild card characters like an asterisk (*), question mark (?) are not valid while using the SUMPRODUCT formula.
  • There should be the same number of rows & columns in all the array of SUMPRODUCT formula; otherwise, it will return an error.
  • 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.

Recommended Articles

This has been a guide to SUMPRODUCT with Multiple Criteria in Excel. Here we discuss how to use SUMPRODUCT Function with Multiple Criteria in Excel along with examples and downloadable excel template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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