### Advanced-Excel

# How to Sumproduct function work in different condition

# 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

- Excel1 year ago
Different components of Ms Excel worksheet

- Excel1 year ago
How to use Best Fit or Auto Fit features in Excel

- Excel1 year ago
What are the types of data you enter in MS Excel

- Advanced-Excel12 months ago
What is data validation in excel how to use data validation?

- PowerPoint9 months ago
How to create custom animation sound or speed in PowerPoint 2016

- Excel1 year ago
What is Excel & importance of Excel worksheet

- Word11 months ago
How to page setup in ms word 2016

- Advanced-Excel12 months ago
Advance conditional formatting excel 2016