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:
    Sales data for SUMPRODUCT example in Excel
  • In this case, we'll enter the SUMPRODUCT formula in a cell below the table:
    Excel's SUMPRODUCT function - worked example
  • 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.

Hidden from Standard Layout
Hidden from Landscape tablet
Hidden from Portrait tablet

Hidden from Standard Layout
Hidden from Landscape smalltouch
Hidden from Portrait smalltouch

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.

Add a comment to this lesson