Math & Trig
Sum matching rows AND columns in a 2D matrix (SUMPRODUCT outer product)
Aggregate a 2D crosstab when BOTH row labels and column headers can repeat (e.g., multiple "North" rows across multiple "Q1" columns).
Sum every matching intersection in a numeric matrix
A single INDEX/MATCH lookup selects one intersection. Here the task is different: sum every intersection of matching row and column labels, including repeated labels. A vertical row mask multiplied by a horizontal column mask selects the matching cells.
Enter 2D boolean outer-product broadcasting inside SUMPRODUCT: multiplying vertical (A2:A4 = F2) (3×1) by horizontal (B1:D1 = G2) (1×3) automatically broadcasts into a 3×3 1/0 mask! Multiplying by B2:D4 sums every intersecting cell in one formula!
| Region (A) | Q1 Direct (B="Q1") | Q2 Direct (C="Q2") | Q1 Partner (D="Q1") |
|---|---|---|---|
| North (Row 2) | $10k (Match!) | $20k | $30k (Match!) |
| South (Row 3) | $40k | $50k | $60k |
| North (Row 4) | $5k (Match!) | $15k | $25k (Match! -> Sum = $70k!) |
2D row-and-column criteria matrix sum with SUMPRODUCT
(A2:A4 = F2) * (B1:D1 = G2) marks 1 only at cells whose row equals F2 AND whose column header equals G2.
=SUMPRODUCT((A2:A4 = F2) * (B1:D1 = G2) * B2:D4)Count how many matrix cells matched both row and column criteria
Omitting the values counts matching positions, including blank positions. Dividing the sum by that count is a mean only when every selected position contains a valid number. This example uses a fully numeric matrix; text or error cells require cleaning first. If no positions match, the count is zero and must not be used as a divisor.
=SUMPRODUCT((A2:A4 = F2) * (B1:D1 = G2))Try the example
Use the original values or change them to test your understanding.