Multiply and add up with SUMPRODUCT

1 / 4

What does the whole order come to? Each row is a quantity times its unit price, and the answer goes in F2.

keyboard Typing formulas is easiest on a computer with a keyboard.

quantity

unit price

total

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How SUMPRODUCT works

What does SUMPRODUCT do?

It multiplies two ranges row by row, then adds up the results. =SUMPRODUCT(B2:B9,C2:C9) is B2×C2 + B3×C3 + … + B9×C9 in one cell.
Why not a helper column?

You could put =B2*C2 in every row and SUM that column. SUMPRODUCT gives the same total without the extra column.
Why not SUM times SUM?

=SUM(B2:B9)*SUM(C2:C9) multiplies the total quantity by the total of the prices, which is a much bigger, meaningless number. Each quantity has to be multiplied by its own price first.
What else can it do?

The ranges must be the same size. A condition inside works as a filter: =SUMPRODUCT((A2:A9="Furniture")*B2:B9*C2:C9) totals one category only. In newer Excel, =SUM(B2:B9*C2:C9) works too.

Know someone fighting with a spreadsheet? Send them this page.