How to Sumproduct function work in different condition Published

on What is SUMPRODUCT?

The SUMPRODUCT function in Excel is a very versatile function that will yield different results depending on the argument entered. Normally, what the SUMPRODUCT function does is multiply the elements of one or more arrays and then add or sum the products together. But SUMPRODUCT is a much more versatile function than this description suggests, and it is not limited to summing up the product.

But by adjusting the form of the argument SUMPRODUCT will count the number of cells in a given range containing data that meet specific criteria.

The syntax of SUMPRODUCT as indicated by Excel is this.

SUMPRODUCT (array1,array2,array3,….)

In this context, an array is a continuous range of cells within a row or column. The number of cells in each array should be the same as in this example: =SUMPRODUCT(B2:B11,C2:C11)

When applied to the following simple table, the result of the formula is 67465: What SUMPRODUCT has done is this:(12 * 650)+(8 * 234)+(25 * 231)+ …. +(22 * 340)=67465 This version of the formula, in which an asterisk replaces the comma, achieves the same result:

=SUMPRODUCT(B2:B11*C2: C11)

It follows that the sign replacing the comma separating the arrays determines the mathematical operation that
SUMPRODUCT engages the pairs of values in the two arrays.

Thus this SUMPRODUCT returns the result 3353:
SUMPRODUCT(B2:B11+C2:C11)=(12+650)+(8+234)+(25+231)+….+(22+340)
And this one returns the result 1.25:
SUMPRODUCT(B2:B11/C2:C11)=(12/650)+(8/234)+(25/231)+….+(22/340)

Applying a Criterion to SUMPRODUCT

You can include a criterion in a SUMPRODUCT formula. For example, we want to calculate the area wise

Total Sales. We may also solve this problem by using SUMIF Function after adding one filed as Total sales.
SUMPRODUCT with Multiple Criteria.
You may apply more than one Criterion to SUMPRODUCT. For example, in this spreadsheet you want it to
find out the total sales of East Zone if quantity sold more than 20. We may also solve this problem by using SUMIFS function after adding one filed as total sales.

Two Way Lookup by using SUMPRODUCT function:

By using SUMPRODUCT function You can also look up data from two ways. In this example, we have a sales database for the first half of the financial year. We may also solve this problem by using the INDIRECT function & Match Index function.

Solution by using Match and Index Function: Solution by using the indirect function:

Here we need to select the database then click on formula Tab ⇒ Create from a selection