Sumproduct() Function

Microsoft excel defines as Sumproduct Formula as: ‘Returns the sum of the products of corresponding ranges or arrays’. But this function is capable of much more than its definition.

Syntax

The syntax for the Sumproduct function in Microsoft Excel is:

SUMPRODUCT( array1, [array2, ... array_n] )

Parameters or Arguments

array1, array2, ... array_n

The ranges of cells or arrays that you wish to multiply. All arrays must have the same number of rows and columns. You must enter at least 2 arrays and you can have up to 30 arrays.

Excel Sumproduct Formula Examples:

If you have a table as shown in the below image:

Now if you want to apply Sumproduct formula on these values then your formula should be:

=SUMPRODUCT(B2:B11,C2:C11)

This will give you an output = 935. So, internally Sumproduct has multiplied each element of both the arrays and then added it to one another.

Example: (1x11)+(2x12)+(3x13)+(4x14)+(5x15)+(6x16)+(7x17)+(8x18)+(9x19)+(10x20) = 935

Sumproduct Function by Changing Arithmetic Operators:

This is another good thing about the Sumproduct formula. Sumproduct can also be used with user defined mathematical operators instead of the default multiplication operation. An example will make this clear:

Multiplication Sumproduct

Writing a Multiplication Sumproduct function as: =SUMPRODUCT(B2:B11*C2:C11) “Replaced comma with multiplication sign”

This makes sense as it tells excel to use the multiplication operator between the corresponding array elements and then add them up (same as Example 1). So it will also yield 935 in the case of above example.

This is the same thing that you can achieve with the formula: =Sumproduct(B2:B11,C2:C11)

Division Sumproduct

A Division Sumproduct can be used as:=SUMPRODUCT(B2:B11/C2:C11)

In the context of above example it will we implemented as: (1÷11)+(2÷12)+(3÷13)+(4÷14)+(5÷15)+(6÷16)+(7÷17)+(8÷18)+(9÷19)+(10÷20)

And gives and output = 3.312285968

Addition Sumproduct

An Addition Sumproduct can be used as: =SUMPRODUCT(B2:B11+C2:C11)

And Microsoft Excel will implement it as:(1+11)+(2+12)+(3+13)+(4+14)+(5+15)+(6+16)+(7+17)+(8+18)+(9+19)+(10+20)

And the output will be: 210

Subtraction Sumproduct

Similarly a Subtraction Sumproduct can be used as: =SUMPRODUCT(B2:B11-C2:C11)

This will be implemented internally as:(1-11)+(2-12)+(3-13)+(4-14)+(5-15)+(6-16)+(7-17)+(8-18)+(9-19)+(10-20)

And will result into a value = -100

Using Criteria in Sumproduct Calculation:

Now comes the best part of using the Excel Sumproduct Function. Sumproduct formula can also be used in performing selective data operations based on a particular criterion.

For instance: You have a data set as shown in the above image. And you want to perform a Multiplication Sumproduct of two array ranges but only for the category named ‘Fibre’.

Then the formula should be as: =SUMPRODUCT((C2:C11*D2:D11)*(B2:B11="Fibre"))

Excel will search the category ‘fibre’ in the defined range. And will only do the Sumproduct for the values of ‘fibre’ category like: (2x12)+(8x18) which gives the result 168.

Notes:

Ø  The array arguments must have the same dimensions. If they do not, Sumproduct returns the #VALUE! error value.

Ø  Up to 30 arrays can be supplied.

Ø  Sumproduct treats array entries that are not numeric as if they were zeros.

Ø  Sumproduct can return results from a closed external workbook.

Ø  Sumproduct generally is more powerful than SUMIFS.

Ø  Sumproduct can't support wildcard characters.

Ø  If just one array is supplied, the function returns the sum of the values in that array.

References

https://www.techonthenet.com/excel/formulas/sumproduct.php

https://exceljet.net/excel-functions/excel-sumproduct-function

http://www.exceltrick.com/formulas_macros/excel-sumproduct-function/

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/