Excelbud

Owner and site operator for Excelbuddy.com

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.  For some examples of the SUMPRODUCT function in action, consider the sample data and the corresponding results :

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: 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.  Since the values corresponding to false are multiplied by 0, they don’t contribute to the result.  In the data above, if you were interested in only adding 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, then use the SUMPRODUCT function to get your result of 6.