Sumproduct Function in Excel – Multiple Arrays

SUMPRODUCT

The SUMPRODUCT function in Excel is a useful tool when you are working with large datasets or multiple arrays. An array is simply a series of numbers.

SUMPRODUCT takes two or more equally-sized arrays, multiplies the respective values, and adds the results.

The format for the function is =SUMPRODUCT (Array1, Array2, Array3,…).

So, if you were to think about two arrays: {1,2,3} and {5,4,6}, the SUMPRODUCT function would calculate 1*5 + 2*4 +3*6 = 31.

This function can save you a lot of time in your calculations, because you would normally have to perform each multiplication individually and then have another function to sum the products. Now you can do it all in one step.

To see an example of using this function, consider the following sample data and the corresponding results:

Sumproduct

SUMPRODUCT (A2:A5,B2:B5) = 82

SUMPRODUCT (B2:B5,C2:C5) = 6

SUMPRODUCT (A2:A5,B2:B5,C2:C5) = 28

The values in Column C show an example of another way to use the SUMPRODUCT function.  This example shows how to perform computations based on logical TRUE/FALSE values.

If you have an equation set up where when conditions are TRUE = 1 and FALSE = 0, then you could use a SUMPRODUCT function with the 1’s and 0’s as one of your arrays to sum only the values where the condition is true.

The values corresponding to false are multiplied by 0, so they don’t contribute to the result. To only add the values in Column B which were multiples of 2, you could write an IF statement to create the values in Column C corresponding to 1 when TRUE, and 0 when FALSE. You would then use the SUMPRODUCT function to get your result of 6.

To answer any questions on SUMPRODUCT, either comment below or ask in our Forum.

Was this article helpful?

Related Articles

Leave a Reply