Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
SUMPRODUCT
AggregationMultiply arrays element-by-element, then sum — weighted averages and conditional math.
Difficulty
1What is it?
SUMPRODUCT multiplies corresponding items across arrays and adds up the results. It's the natural way to compute weighted averages, and before SUMIFS it was how people did conditional sums (by multiplying TRUE/FALSE arrays).
2What it looks like
SUMPRODUCT(array1, [array2], …)- array1
- The first range.
- [array2]
- Optional more ranges, multiplied element-by-element.
3When you use it
- Weighted average price: SUMPRODUCT(units, price) / SUM(units).
- Conditional sum in older Excel: SUMPRODUCT((region="West")*sales).
- Blended rate or weighted score across line items.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Edit the units (B2:B4) and watch the blended price recompute.
=SUMPRODUCT(B2:B4, C2:C4) / SUM(B2:B4)$34 avg price| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Units | Price | Result |
| 2 | Widget | $20 | $34 avg price | |
| 3 | Gadget | $35 | ||
| 4 | Gizmo | $50 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#VALUE!Arrays are different sizes, or contain text.Fix: Make every array the same shape; coerce text/blanks (e.g. multiply by 1).
6Better functions & alternatives
- SUMIFS — Cleaner and faster for straightforward conditional sums.
- Dynamic arrays — FILTER/SUM combos can be clearer in Microsoft 365.
Want SUMPRODUCT already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →