How to Use SUMPRODUCT in Excel
SUMPRODUCT multiplies arrays and returns one total. Here's the syntax, the most practical finance examples, and the mistakes that cause wrong results.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
SUMPRODUCT is the Excel function you use when a total depends on more than one value at the same time.
The classic example is quantity times price. But it is also useful for weighted averages and filtered totals where several conditions need to be true at once.
That makes it especially practical in operating sheets like an expense tracker or a small business budget template.
The syntax
=SUMPRODUCT(array1, [array2], [array3], ...)- array1 — the first range of values
- [array2] — the second range to multiply against the first
- [array3] — optional extra ranges or tests
Excel multiplies matching positions across the arrays and then adds the results into one total.
Basic example
If column B contains quantity and column C contains unit price:
=SUMPRODUCT(B2:B6, C2:C6)Excel multiplies each row's quantity by its unit price and then adds those row totals together.
That is often cleaner than creating a helper column just to calculate line-item totals first.
Use SUMPRODUCT for conditional totals
SUMPRODUCT is also useful when you need to total only the rows that meet specific conditions.
If column A is Status, column B is Category, and column C is Amount:
=SUMPRODUCT((A2:A100="Approved")*(B2:B100="Travel")*(C2:C100))That returns the total amount for rows where Status is Approved and Category is Travel.
The logic works because Excel treats TRUE as 1 and FALSE as 0 once the conditions are multiplied together.
✦ TIP
SUMPRODUCT is useful when you need multi-condition math but do not want to build helper columns for every intermediate step.
Use SUMPRODUCT for weighted averages
Weighted averages are another common use case.
If column B contains scores and column C contains weights:
=SUMPRODUCT(B2:B6, C2:C6)/SUM(C2:C6)That multiplies each score by its weight, adds the weighted values together, and divides by the total weight.
This pattern shows up in pricing models, forecast assumptions, and budget review where some rows should count more than others.
Common mistakes
| Mistake | What goes wrong | Fix |
|---|---|---|
| Arrays are different sizes | Wrong result or #VALUE! | Make every referenced range the same length |
| Using full-column references everywhere | Slow sheets | Limit the ranges to the part of the sheet that actually has data |
| Forgetting the final numeric array in a conditional formula | You get a count-like result instead of a dollar total | Multiply the conditions by the amount or score range |
| Expecting SUMPRODUCT to behave like SUMIFS automatically | The logic becomes hard to read | Use SUMIFS for simpler criteria totals and SUMPRODUCT when the math genuinely needs it |
⚠ WATCH OUT
SUMPRODUCT is powerful, but it is easy to make the formula harder to audit than necessary. If SUMIFS or a helper column makes the sheet clearer, use the simpler option.
SUMPRODUCT vs SUMIFS
Use SUMIFS when the job is simply "sum the rows that meet these criteria."
Use SUMPRODUCT when you need multiplication, weights, or more custom logic inside the total.
For example, an agency budget template may use SUMIFS for straightforward expense totals by month, but SUMPRODUCT for weighted assumptions or multi-input calculations that do not fit a simple criteria pattern.
The Griddy way
SUMPRODUCT is useful, but it gets error-prone once conditions, weights, and real sheet ranges start piling up. It is easy to reference mismatched ranges or write logic nobody wants to debug later.
"Calculate the weighted average margin for these client accounts and only include approved projects"
Griddy can write the formula, place it in the right cell, and keep the sheet readable enough for someone else to review later.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Use SUMPRODUCT when totals depend on more than one input
SUMPRODUCT is especially useful in operating budgets and finance sheets where totals rely on multiplying quantities, rates, weights, or filtered values in one formula.
Small Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Small Business Budget for Agencies
Plan agency retainers, project revenue, payroll, contractors, software, and gross margin in one free operating budget spreadsheet built for service teams.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Budget Tracker
Track income, expenses, and savings in one place. Line items, budgeted vs actual totals, and monthly net savings — free to use in your browser.