Order total
| A | B | |
|---|---|---|
| 1 | Qty | Price |
| 2 | 2 | 10 |
| 3 | 3 | 5 |
| 4 | 1 | 40 |
Formula: D2: =SUMPRODUCT(A2:A4,B2:B4)
Result: 75
2×10 + 3×5 + 1×40 = 75.
Google Sheets function
SUMPRODUCT in Google Sheets
SUMPRODUCT works on arrays natively in Google Sheets, so it needs neither ARRAYFORMULA nor a special key combination.
Syntax (same in Google Sheets)
SUMPRODUCT(array1, [array2], …)In Sheets, Ctrl+Shift+Enter wraps a formula in ARRAYFORMULA, but SUMPRODUCT does not need it.
For a fuller explanation of the arguments and common errors, see the SUMPRODUCT Excel guide.
Worked examples in Google Sheets
Weighted score
| A | B | |
|---|---|---|
| 1 | Score | Weight |
| 2 | 90 | 0.5 |
| 3 | 80 | 0.3 |
| 4 | 70 | 0.2 |
Formula: D2: =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)
Result: 83
Dividing by the total weight gives the weighted average.
Conditional sum
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | East | Ana | 1200 |
| 3 | West | Ben | 850 |
| 4 | East | Cara | 430 |
| 5 | North | Dan | 970 |
| 6 | West | Eli | 1500 |
| 7 | East | Ana | 610 |
Formula: E2: =SUMPRODUCT((A2:A7="East")*C2:C7)
Result: 2240
The comparison gives TRUE or FALSE per row; multiplying turns it into 1 or 0, so only East amounts survive.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =SUMPRODUCT(C2:C7,D2:D7)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Price | Result | |
| 2 | East | Pen | 120 | 2.5 | 2885 | |
| 3 | West | Pen | 80 | 2.5 | ||
| 4 | East | Book | 45 | 12 | ||
| 5 | North | Book | 60 | 12 | ||
| 6 | West | Lamp | 30 | 25 | ||
| 7 | East | Lamp | 15 | 25 |
Result in F2: 2885
Frequently asked questions
- Do I need ARRAYFORMULA around SUMPRODUCT in Google Sheets?
- No. SUMPRODUCT already evaluates its arguments as arrays, including conditions such as (A2:A7="East"), so you can use it as it is shown here.
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.