Math
SUMPRODUCT with criteria: multiply and filter arrays without a helper column
Calculate filtered revenue across quantity and unit-price columns in one SUMPRODUCT formula using boolean (TRUE/FALSE) array multiplication.
Why SUMIFS cannot multiply two columns on the fly
SUMIFS requires an existing single sum_range column. When an order table stores Units in column C and Unit Price in column D without a Line Total column, SUMPRODUCT multiplies C2:C6 by D2:D6 row by row and sums the products directly.
Multiplying boolean tests such as (A2:A6=F2)*(B2:B6=G2) converts TRUE to 1 and FALSE to 0, zeroing out non-matching rows before summing.
| Channel (A) | ProductGroup (B) | Units (C) | UnitPrice (D) | Implied Line |
|---|---|---|---|---|
| Direct | Valves | 12 | 25 | 300 |
| Partner | Valves | 20 | 22 | 440 |
| Direct | Pumps | 4 | 180 | 720 |
| Direct | Valves | 8 | 25 | 200 |
| Direct | Valves | -2 | 25 | -50 (return credit) |
Unfiltered and multi-criteria array totals
Across all five rows, =SUMPRODUCT(C2:C6, D2:D6) totals 300 + 440 + 720 + 200 - 50 = 1,610. Filtering to Channel = "Direct" and ProductGroup = "Valves" keeps rows 2, 5, and 6: 300 + 200 - 50 = 450.
=SUMPRODUCT((A2:A6=F2)*(B2:B6=G2)*C2:C6*D2:D6)Comma arguments vs asterisk multiplication on text cells
Passing numeric ranges as separate comma arguments—SUMPRODUCT((A2:A6=F2)*(B2:B6=G2), C2:C6, D2:D6)—treats stray text labels in C2:C6 or D2:D6 as 0, whereas multiplying *C2:C6*D2:D6 directly raises #VALUE! if a numeric cell contains text. Also ensure every array has the exact same row count.
=SUMPRODUCT((A2:A6=F2)*(B2:B6=G2), C2:C6, D2:D6)Try the example
Use the original values or change them to test your understanding.