# How to multiply two columns and then sum in Excel

Use SUMPRODUCT: `=SUMPRODUCT(B2:B7,C2:C7)` multiplies each quantity by its price and adds the results.

**Excel:** `=SUMPRODUCT(B2:B7,C2:C7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Item | Qty | Unit price |  | Order total |
| Notebook | 24 | 3.75 |  |  |
| Gel pens (12) | 6 | 8.4 |  |  |
| Stapler | 3 | 14.99 |  |  |
| Folders (25) | 4 | 11.5 |  |  |
| Sticky notes | 10 | 2.15 |  |  |
| Desk lamp | 2 | 32 |  |  |

Formula in F1: `=SUMPRODUCT(B2:B7,C2:C7)`

Result: $316.87

## 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.

## Other ways

- `=SUM(B2:B7*C2:C7)`: 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!.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/multiply-two-columns-and-sum