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 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?

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: 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 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, 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:
• 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  to calculate the count with multiple criteria.

• 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,

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