The SUMPRODUCT function in Excel is a useful tool when you are working with large data sets 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. 

Let’s look at the syntax:

 =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 sum the products.  Now you can do it all in one step.

Here are some examples of how to use the SUMPRODUCT function. Please review the sample data and the corresponding results. I have also attached a worksheet to follow along.

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

Leave a Reply

XLOOKUP Function in Excel

XLOOKUP replaces VLOOKUP AND HLOOKUP permanently! Speed up your spreadsheet by changing your default lookup function. Add a 4th and 5th argument!

Read More »
Scroll to Top