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
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:
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
=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.
SUMPRODUCT Excel Function Video
Recommended Articles
This has been a guide to SUMPRODUCT Function in Excel. Here we discuss the SUMPRODUCT Formula and how to use it along with excel example and a downloadable template. You may also look at these useful functions in excel –
- SUMPRODUCT with Multiple Criteria
- SUBTOTAL Excel Function
- AGGREGATE Function
- SIGN Excel Function
- Redo Shortcut in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
