Excel Sumproduct

The SUMPRODUCT function is one of Excel's most powerful functions. The example below clearly illustrates this function.

Sumproduct Example

The Excel file consists of 3 columns. One for the Product, one for the Quantity and one for the Price.

Question: how do we calculate the total amount spent if we order these products? Answer: we use the SUMPRODUCT function.

Sumproduct Function

What we need is a function that calculates (8 * 800) + (2 * 100) + (2 * 50) + (6 * 250). The SUMPRODUCT function exactly does this.

1. Select cell C7.

2. Type =SUMPRODUCT(

3. Excel asks us to enter the first range. Select range("B2:B5").

4. Excel asks us to enter the second range (don't forget to enter a comma first!). Select range("C2:C5") and close with a parenthesis.

Result:

Conclusion: the total amount spent if we order these products equals 8200.

Sumproduct Remarks

The ranges that you give Excel as input must have the same dimensions.

For example, the following ranges are not valid. Excel displays the #VALUE! error when you press Enter.

The SUMPRODUCT function treats any entries that are not numeric as if they were zeros.

For example,

Did you like this free Excel tutorial? Show your appreciation, vote for us.

Thank you very much! We've got many more free Excel tutorials for you. Read on, learn, and enjoy!

Go to Top: Excel Sumproduct    |    Go to Next Tutorial: Excel Pmt