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.
Math
SUMPRODUCT
Sum of pairwise products.
Syntax
SUMPRODUCT(array1, [array2], …)Example: =SUMPRODUCT(B2:B6,C2:C6)
Available in: All versions
SUMPRODUCT multiplies matching items in two or more ranges and adds up the products: =SUMPRODUCT(array1, array2, …). It handles weighted totals and conditional sums in one step, with no helper columns and no array-entry.
When to use it
- You need quantity times price, summed, without a helper column.
- You compute a weighted average, such as grades with weights.
- You sum or count with conditions that SUMIFS cannot express, such as OR logic or tests on calculated values.
- You need a total that works in every Excel version.
Arguments explained
- array1. The first range or array. SUMPRODUCT multiplies the values position by position and adds the products.
- array2, array3, …. Optional further arrays, up to 255. All must have exactly the same dimensions, otherwise the result is #VALUE!.
- Conditions. Comparisons inside the arrays produce TRUE/FALSE, which you turn into 1 and 0 by multiplying, for example SUMPRODUCT((A2:A9="East")*(C2:C9)).
- Text cells. Non-numeric entries are treated as zero when passed as separate arguments, but cause errors when used inside arithmetic.
Worked examples
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
Common errors and how to fix them
- #VALUE!. The arrays have different sizes. All arguments must have the same number of rows and columns.
- Text counts as zero. Text in a numeric array is treated as 0 when passed as separate arguments. Clean the data.
- Wrong result with comma and multiplication. Separating arrays with commas treats text as 0, while multiplying with * returns #VALUE!. Know which you need.
- Slow on huge ranges. Calculated arrays on whole columns are heavy. Restrict ranges.
Related functions
Frequently asked questions
- Why is SUMPRODUCT used for conditional sums?
- It evaluates arrays natively, so conditions like OR logic or text length tests work without Ctrl+Shift+Enter.
- Can SUMPRODUCT count?
- Yes: =SUMPRODUCT(--(A2:A7="East")) counts matching rows.
- What does the double minus do?
- It turns TRUE and FALSE into 1 and 0 so they can be added.
- SUMPRODUCT or SUMIFS?
- SUMIFS is faster and simpler for plain AND conditions; SUMPRODUCT is for everything else.