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

Leave a Reply

Scroll to Top