Microsoft Excel Functions: SUMPRODUCT

SUMPRODUCT is a function which multiplies the corresponding components in selected arrays, and returns the sum of those calculations

Download the accompanying file(s) for this article.

Before learning the SUMPRODUCT function most people would take the longer approach to calculating the products of two columns of data. Granted, the user only needs to enter the formula once and drag it down the column, but it will lead to an overly cluttered spreadsheet and extra time spent checking formulas.

SUMPRODUCT Syntax

=SUMPRODUCT(array1, [array2], [array3], etc...)

array1 is mandatory. It is the list of components that you wish to multiply and then add. If there is only 1 array the result will be the sum of the array

array2, array 3 are optional arguments, you can have up to 255 optional arrays.

For example, =SUMPRODUCT(array1, array2, array3) would carry out the calculation:

Array1 X Array 2 X Array 3

 

SUMPRODUCT in Action

Prior to learning how to use SUMPRODUCT, when wanting to know the result of column A * column B, most people’s worksheet would look like this:

SUMPRODUCT

They would add a new column and multiply each row in column A by the corresponding row in Column B, then sum up the total of the new column. It works, but it could be better.

Now knowing what you do about SUMPRODUCT you only need to have one calculation cell; the total. To arrive at the same answer using SUMPRODUCT your formula is written:

=SUMPRODUCT(A3:A12,B3:B12)

SUMPRODUCT

What SUMPRODUCT is doing is saving you a lot of time by carrying out this calculation:

=A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8+A9*B9+A10*B10+A11*B11+A12*B12

 

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Formula

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *