How to multiply two columns and then sum in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B7,C2:C7)Use SUMPRODUCT: =SUMPRODUCT(B2:B7,C2:C7) multiplies each quantity by its price and adds the results.
- Result on the example
- $316.87 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMPRODUCT pairs the ranges row by row: 24 × 3.75, 6 × 8.40, and so on. It adds the six products, so you get the order total without a helper column of line totals.
=SUMPRODUCT(B2:B7,C2:C7)Result316.87
B2:B76 rows × 1 col: 24, 6, 3, 4, …A range: 6 cells in one column.
C2:C76 rows × 1 col: $3.75, $8.40, $14.99, $11.50, …A range: 6 cells in one column.
SUMPRODUCT(B2:B7,C2:C7)316.87SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B76 rows × 1 col: 24, 6, 3, 4, … - array2
C2:C76 rows × 1 col: $3.75, $8.40, $14.99, $11.50, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(B2:B7,C2:C7). We ran it in Google Sheets mode on the same data and it returns $316.87.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B2:B7*C2:C7)Excel only | $316.87 | Excel 365 calculates the array directly. Older Excel needs Ctrl+Shift+Enter, and Google Sheets needs ARRAYFORMULA. |
Common mistakes
=SUM(B2:B7)*SUM(C2:C7)multiplies the totals, not the rows, and gives a far bigger number.- The ranges must be the same size, or SUMPRODUCT returns #VALUE!.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Multiply two columns and then sum
F1, calculate the order total: each quantity times its unit price, added up.Questions
How do I add a condition?
Multiply in a test: =SUMPRODUCT((A2:A7="Stapler")*B2:B7*C2:C7).
How do I show each line total too?
Put =B2*C2 in D2 and fill down; the sum of that column matches SUMPRODUCT.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.