Excel advanced sumproduct weighted average conditional array

SUMPRODUCT for Conditional Calculations in Excel

SUMPRODUCT for Conditional Calculations is a Excel function that this calculates a weighted average: sum of (price * quantity) divided by sum of quantities, but only for electronics. Formula Genius generates and validates this formula automatically from a plain-English prompt.

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

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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples