Use SUMPRODUCT to multiply two columns and then add up the results
The SUMPRODUCT function is perfect if you have two or more columns of data (e.g. Quantity Sold and Price Per Unit) and you want to find the total value of the columns multiplied together. Without the SUMPRODUCT function, you'll find yourself having to create a third column that contains a formula that multiplies each row together, and then using SUM to add up the values in the third column. This lesson shows you how to use SUMPRODUCT to do all that with just one formula.
The syntax of the SUMPRODUCT function is as follows:
=SUMPRODUCT(array1, [array2], [array3],...)
where array1 is the data in the first column, array2 is the data in the second column and so on.
Some points to note:
- You only need array1 for the function to work BUT if you only have array1 then SUMPRODUCT works the same way as SUM
- Array2 and Array3 are both optional. In fact, you can have 255 arrays in one SUMPRODUCT formula
- However many arrays you have, they must all be the same shape. You can't have array1 containing 43 cells in a column, and array2 having only 42 cells - Excell will return a #VALUE error.
- If any cells have text in them, the SUMPRODUCT functions treats them as containing zeros.
Example of SUMPRODUCT in action
- In this example, we'll use the following example where we want to find the total value of sales for Monday:
- In this case, we'll enter the SUMPRODUCT formula in a cell below the table:
- The second line shows the formula that was used to get the result in D16.
- SUMPRODUCT works by multiplying the quantity by the unit price for each row, and then returning the total for all rows in D16.
As noted above, you could extent this function to include a third column. For example, the third column could include a calculation to determine a percentage discount if the quantity sold exceeds five units.