SUMPRODUCT Excel Function (Formula,Examples)

SUMPRODUCT on Excel (Table of Contents)

SUMPRODUCT in Excel
SUMPRODUCT Formula in Excel
How to Use?

SUMPRODUCT in Excel

The SUMPRODUCT function in Excel is 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 in Excel is used to calculate the sum of the products of corresponding numbers in one or more ranges/arrays. In other words, 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.

SUMPRODUCT Formula in Excel

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.

How to Use SUMPRODUCT Function in Excel?

The SUMPRODUCT in Excel is very simple and easy to use. Let understand the working of SUMPRODUCT function in Excel by some examples. The SUMPRODUCT in Excel can be used as worksheet function and as VBA function.

SUMPRODUCT in Excel Example #1

In the first SUMPRODUCT example 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.

SUMPRODUCT in Excel 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:

=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.

SUMPRODUCT in Excel Example #3

We can use the SUMPRODUCT function in Excel 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)

SUMPRODUCT in Excel Example #4

The SUMPRODUCT function in Excel 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 About the SUMPRODUCT Function in Excel

SUMPRODUCT Formula in Excel is used to calculate a weighted average.
SUMPRODUCT through the #VALUE error when the arrays provided have different dimensions/range in the array.
The SUMPRODUCT function in Excel 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.

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

SUMPRODUCT Excel Function