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

FLOOR Function

The FLOOR function in Excel rounds a given number down to the nearest specified multiple. Unlike the MROUND function, the

Read More »

VBA Comment Structure

This section of the VBA learning course reviews the Comment Structure. In every programming language, you need to have the

Read More »
Scroll to Top