Math & Trig
Weighted average with multiple criteria (SUMPRODUCT + SUMIFS)
Calculate volume-weighted unit price or yield strictly for rows matching Region, Channel, or Status filters.
Filter both numerator and denominator by the exact same criteria
AVERAGEIFS averages unit prices without weighting by quantity—treating a 10-unit sample order the same as a 500-unit wholesale contract.
To compute a conditional weighted average for "North" + "Closed" rows, multiply boolean criteria masks inside SUMPRODUCT((Region="North") * (Status="Closed"), Qty, UnitPrice) and divide by SUMIFS(Qty, Region, "North", Status, "Closed").
| Region (A) | Status (B) | Qty (C) | UnitPrice (D) | Included in North+Closed? |
|---|---|---|---|---|
| North | Closed | 100 | 20 | Yes (2,000 rev / 100 qty) |
| North | Open | 200 | 15 | No (Open) |
| North | Closed | 300 | 16 | Yes (4,800 rev / 300 qty) |
| South | Closed | 400 | 12 | No (South) |
Multi-criteria weighted average formula
Matching North + Closed rows total (2,000 + 4,800) / (100 + 300) = 6,800 / 400 = $17.00/unit (vs unweighted AVERAGEIFS = $18.00).
=LET(w, SUMIFS(C2:C5, A2:A5, "North", B2:B5, "Closed"), IF(w=0, "No matching weight", ROUND(SUMPRODUCT((A2:A5="North") * (B2:B5="Closed"), C2:C5, D2:D5) / w, 2)))Contrast unweighted AVERAGEIFS
AVERAGEIFS ignores order volume (20 + 16) / 2 = 18.00.
=AVERAGEIFS(D2:D5, A2:A5, "North", B2:B5, "Closed")Inputs and interpretation
Require numeric nonnegative quantities and complete numeric prices. A zero matching weight has no defined weighted mean. Multiplication by a false criteria mask does not suppress spreadsheet errors elsewhere in the arrays. Keep numerator and denominator criteria identical; SUMIFS wildcard criteria differ from literal equality when generalized to user-entered patterns.
Try the example
Use the original values or change them to test your understanding.