SUMPRODUCT on Excel (Table of Contents)
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 Function 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.
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 by some examples.
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.
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.
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.
- The SUMPRODUCT 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 in excel and how to use SUMPRODUCT Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- SUMPRODUCT with Multiple Criteria
- Arrays in Excel VBA – Top 5 Different Types
- How to use VLOOKUP Multiple Criteria
- How to use MONTH Function
- How to use AVERAGE Function
- How to use VLOOKUP Function
- How to use SUBTOTAL Excel Function
- How to use ROUND Excel Function
- How to use AGGREGATE Excel Function
- How to use SIGN Excel Function
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
