SUMPRODUCT in Excel (Formula,Examples)

SUMPRODUCT in Excel

The SUMPRODUCT function has categorized as Math or Trig function in excel used to multiple the array or range and return the sum of a product. It is used to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of the products of corresponding numbers in one or more ranges/arrays. In other words, the SUMPRODUCT function multiplies the corresponding components in the given arrays and returns the sum of those products.

You can extend the functionality of SUMPRODUCT Formula by using other functions inside it.

Syntax

SUMPRODUCT Formula has one array as compulsory parameter and n numbers of arrays as optional parameters i.e. arrary1 and [array2]…

Compulsory Parameter:

array1: This parameter is the first array or range that will be multiplied then added.

Optional Parameter:

[array2]: The second array or ranges that will be multiplied then added. It is optional
[array3]: The third and onwards arrays or ranges that will be multiplied then added.

Examples to use SUMPRODUCT Function in Excel

You can download this SUMPRODUCT Function Excel Template here – SUMPRODUCT Function Excel Template

Example #1

Let’s consider a set of products from A to J and their price and units listed in the subsequent columns now calculate the sum of all the listed products by SUMPRODUCT formula. And the o/p of SUMPRODUCT function i.e. =SUMPRODUCT($B$5:$B$14,$C$5:$C$14) = 20,0600 as shown in the below table.

Example #2

In this example, we take a month and region wise sale data and determine the total sales for the west region by applying the SUMPRODUCT Formula as follows:

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

=SUMPRODUCT(–($C$20:$C$31=”West”),$D$20:$D$31) here – – is used to convert true false array into 0’s and 1’s.

The output will be 1,500 as shown in the below table.

Example #3

We can use the SUMPRODUCT function to calculate a weighted average where each product have assigned a weight. Suppose we are given the following data.

The output will be: 55.80%=SUMPRODUCT(C36:C46,D36:D46)/SUM(D36:D46)

Example #4

The SUMPRODUCT function can be used with multiple criteria. Consider the given set of data as shown in the below table. Now calculate the total January sales for the west region. Then apply the Excel SUMPRODUCT formula- =SUMPRODUCT((D53:D64*E53:E64)*(B53:B64=”Day shift”)*(C53:C64=”West”))

And the output will be – 10.

The SUMPRODUCT function in Excel can be used as a VBA function.

Sub abc()
MsgBox Evaluate(“=SUMPRODUCT(($A$1:$A$5=””Tanuj””)*($B$1:$B$5))”)
End Sub

Things to Remember

SUMPRODUCT formula is used to calculate a weighted average.
SUMPRODUCT through the #VALUE error when the arrays provided have different dimensions/range in the array.
This function can accept non-numeric entries that are used in the array and consider them as zeroes.
SUMPRODUCT accepts up to 255 parameters in latest versions of Excel above 2007 and 30 in earlier Excel versions.
Logical tests inside arrays will create TRUE and FALSE values. In most cases, it is advisable to convert them to 1’s and 0’s.
SUMPRODUCT and MONTH can be used to get a sum of values for a given month.