December 4, 2020

SUMPRODUCT function in Google Sheet

SUMPRODUCT function is used when you want to first multiply two or more ranges or arrays together and then get its sum in both Excel & Google Sheet.

It returns a number that is the result of the sum of the product of two or more arrays. 

=SUMPRODUCT(array1, [array2], ...)
  • array1 – The first array or range whose components you want to first multiply and then add.
  • array2 – [optional] The second array or range to first multiply and then add. There can be a maximum up to 255 arrays that can be used in SUMPRODUCT function.

In the below example we have 2 arrays. Once contains odd numbers and another contains even.

In Cell D2 we have used the below formula.

=SUMPRODUCT(A2:A5,B2:B5)

It first multiplies each odd number from the array A2:A5 with it’s corresponding even number from the array B2:B5 and then finally sum up the values which results 100= (1*2)+(3*4)+(5*6)+(7*8)

Now in the next example we have modified all integers to decimal numbers to show SUMPRODUCT can handle decimal numbers as well.

Here we have used the below formula in cell D2 to evaluate each value in B2:B5 for the condition if the value is less than 6.1. 

=SUMPRODUCT(A2:A5,--(B2:B5<6.1))

The condition will return TRUE or FALSE which in turn converted to 1 or 0 by the preceding double negative (–). Then the value (1 or 0) will be multiplied with the corresponding values in B2:B5 and then finally sum up to get the result as 5.28=(1.37*1)+(3.91*1)+(5.05*0)+(7.35*0) since 2.4 and 4.86 are less than 6.1, so only for the first 2 values in array B2:B5, the condition will return 1.

Notes :

  • The size of all the array arguments must be the same. Otherwise, SUMPRODUCT will generate a #VALUE! error.
  • Non-numeric items are treated as zeros.

Leave a Reply

Your email address will not be published. Required fields are marked *