A plain AVERAGE treats a 1-unit buy and a 1,000-unit buy equally. A weighted average uses quantity as the weight, giving the true blended price.
SUMPRODUCT does the row-by-row multiply-and-add in one step.
The example
Three purchases at different prices and quantities. We want the true average cost per unit.
| A | B | C | |
|---|---|---|---|
| 1 | Lot | Price | Qty |
| 2 | A | $10 | 100 |
| 3 | B | $12 | 300 |
| 4 | C | $15 | 100 |
| 5 | Weighted avg | $12.20 |
The formula
SUMPRODUCT multiplies each price by its quantity and adds the results; dividing by total quantity gives the blended price:
How it works
Why it differs from a plain average:
- SUMPRODUCT computes 10×100 + 12×300 + 15×100 = 1,000 + 3,600 + 1,500 = $6,100 spent.
- SUM of quantities is 100 + 300 + 100 = 500 units bought.
- Divide: $6,100 ÷ 500 = $12.20 per unit — the true blended cost.
- A plain AVERAGE of $10, $12, $15 would give $12.33, ignoring that lot B was three times the size.
This is exactly how weighted-average inventory costing works, and how a blended interest or grade average is computed.
Try it: interactive demo
Enter three prices and quantities; see the weighted average versus a plain average.
Variations
Weighted average grade
Same formula with scores and credit weights.
Ignore blank or zero quantities
SUMPRODUCT naturally skips zero-weight rows since they contribute nothing.
Pitfalls & errors
The two ranges must be the same length. Mismatched sizes make SUMPRODUCT return #VALUE!.
Do not use a plain AVERAGE when the items have different weights — it can be materially wrong, as the $12.33 vs $12.20 gap shows.
Practice workbook
Frequently asked questions
When should I weight an average?
What does SUMPRODUCT actually do?
Can I weight by something other than quantity?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class