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.
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:
When applied to the following simple spreadsheet the result of the formula is 67465:
What SUMPRODUCT has done is this:
The same result is achieved by this version of the formula, in which an asterisk replaces the comma:
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:
And this one returns the result 1.25:
Applying a Criterion to SUMPRODUCT
You may include a criterion within a SUMPRODUCT formula. For example, We want to calculate Zone wise
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