Advanced-Excel

How to Sumproduct function work in different condition

0
How to Sumproduct function work in different condition

What is SUMPRODUCT?

The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the argument entered. Normally, what the SUMPRODUCT function does is multiply the elements of one or more array 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 the summation of products.

But by adjusting the form of the argument SUMPRODUCT will count the Number of cell 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 cell in a row or column. the number of cells must be the same in each array, as in this example of a formula:

=SUMPRODUCT(B2:B11,C2:C11)

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

=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 may include a criterion within a SUMPRODUCT formula. For example, We want to calculate Zone 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 may also lookup data from two way.in this example, we have a database on sale figure 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

 

Subhendu Bera
I am Subehndu Bera From India I love to write on a web. always looking for something new to learn. interested in technology and science, and right now I will be pursuing a Bcom in Kolkata.

How to use dynamic range VLookup function in ms excel 2016

Previous article

How to placing a border around a page in Microsoft word 2016

Next article

You may also like

Comments

Leave a reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.