Welcome to Excel Avon
Introduction of SUMPRODUCT Formula
Summary
In this post you can learn How to use SUMPRODUCT formula. The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. As a financial Analyst, SUMPRODUCT is very handy person, as it can handle arrays in different ways & help in comparing data in two or more than two ranges. This formula returns sum of multiplications for corresponding items in ranges or arrays.
Formula
=SUMPRODUCT(Array1,[array2],[array3].........
Arguments
array1 [required] – This is the first array or range to multiply, then add.
array2 [optional] – This is the second array or range to multiply, then add.
array3 [optional] – This is the third array or range to multiply, then add.
SUMPRODUCT Formula in Excel
Example 1
SUMPRODUCT is used to calculate a weighted average. It also helps in calculating data with multiple criteria. So you guys can see that we have data in three columns as an example, so now we have to use SUMPRDUCT formula in them.
To understand the uses of the SUMPRODUCT function, let’s consider a few examples:
So now I will apply SUMPRODUCT formula. This formula returns sum of multiplications for corresponding items in ranges or arrays.
The SUMPRODUCT formula is to be written as =SUMPRODUCT(B3:B17,C3:C17,D3:D17)
After writing the formula, we will press enter, we will know the SUMPRODUCT of all the three columns.
Note-
-
- It is used to calculate a weighted average
- 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.
- The SUMPRODUCT function will treat non-numeric entries that are part of the array as zeroes.
- SUMPRODUCT formula helps look up a value at the intersection of a given row and column.
