SUMPRODUCT multiplies arrays together and adds the results — perfect for a total like quantity × price in one cell. Its hidden superpower is counting and summing on multiple conditions without SUMIFS, and even doing OR logic.
The example
Line totals (qty × price) summed in one step.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Price |
| 2 | Widget | 3 | $10 |
| 3 | Gadget | 2 | $20 |
| 4 | Cable | 5 | $5 |
| 5 | Order total: | $95 |
The formula
The order total (3×10 + 2×20 + 5×5):
How it works
SUMPRODUCT does element-wise multiply, then sum:
- It multiplies the arrays row by row:
B2*C2,B3*C3,B4*C4. - Then it adds all those products into a single total — no helper column of line totals needed.
- Give it conditions instead of values to count/sum with logic:
=SUMPRODUCT((A2:A10="West")*(B2:B10))sums West rows. - Multiplying conditions is AND; adding them is OR — which is why SUMPRODUCT predates and outflexes SUMIFS.
The classic conditional count: =SUMPRODUCT((region="West")*(amount>100)) counts West rows over $100. Each comparison makes a TRUE/FALSE array; multiplying turns them to 1/0 and SUMPRODUCT adds the hits.
Try it: interactive demo
Edit quantities; the order total updates.
Variations
Conditional sum
Total West-region amounts:
Conditional count
Count rows meeting two conditions:
OR logic
West OR East (add the conditions):
Pitfalls & errors
#VALUE! from mismatched sizes. All arrays in a SUMPRODUCT must have the same dimensions, or it errors. Keep the ranges the same height (and width).
Text in a multiplied range breaks it. Multiplying a condition by a column with text gives #VALUE!. Wrap with -- or ensure the summed range is numeric.
Use * for AND, + for OR. Mixing them up changes the logic. For OR, make sure conditions don’t double-count overlapping rows.
Practice workbook
Frequently asked questions
What does SUMPRODUCT do in Excel?
How do I use SUMPRODUCT with conditions?
Why does SUMPRODUCT return #VALUE!?
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