SUMPRODUCT is a function which multiplies the corresponding components in selected arrays, and returns the sum of those calculations
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(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.
=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:
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:
What SUMPRODUCT is doing is saving you a lot of time by carrying out this calculation: