We’re improving Formula Genius. We’re redesigning the app and fixing issues along the way. Sorry for any disruption, and thank you for your patience.
Excel advanced

SUMPRODUCT for Conditional Calculations in Excel

SUMPRODUCT is the Swiss Army knife of Excel formulas. It handles weighted averages, complex conditions, and calculations SUMIFS can't.

Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.

Generate this formula Browse library

The formula

Prompt

"Calculate weighted average price where category is Electronics"

Excel 
=SUMPRODUCT((A2:A100="Electronics")*(B2:B100)*(C2:C100))/SUMPRODUCT((A2:A100="Electronics")*(C2:C100))

This calculates a weighted average: sum of (price * quantity) divided by sum of quantities, but only for Electronics. The boolean condition filters rows to Electronics only.

Step-by-step breakdown

  1. (A2:A100="Electronics") creates TRUE/FALSE array (treated as 1/0)
  2. Multiplying by B2:B100 (prices) zeroes out non-Electronics rows
  3. Multiplying by C2:C100 (quantities) gives price * quantity per row
  4. SUMPRODUCT sums all these products in one step
  5. Dividing by the weighted quantity sum gives the weighted average

Edge cases & warnings

  • Division by zero if no Electronics rows exist — wrap denominator check
  • All ranges must be the same size or you get #VALUE!
  • Mixing full column (A:A) and fixed ranges causes errors — use matching ranges
  • Boolean multiplication is implicit array logic — no Ctrl+Shift+Enter needed

Examples

Prompt

"Electronics: prices [10,20,30], quantities [5,3,2]"

Excel 
Weighted avg = (50+60+60)/10 = 17
Prompt

"All categories: same formula without the filter"

Excel 
Weighted average across all products

Frequently asked questions

When should I use SUMPRODUCT instead of SUMIFS?

Use SUMPRODUCT for: weighted calculations, OR logic across criteria, calculations involving multiplication of filtered ranges, or when you need to combine SUM and IF logic in complex ways.

Is SUMPRODUCT slow?

It can be on very large datasets (100K+ rows). For simple conditional sums, SUMIFS is faster. Use SUMPRODUCT only when SUMIFS can't handle the calculation.

Can't find what you need?

Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.

Generate custom formula See examples