Lookup & Reference
2D Matrix Dynamic Row Sum, Column Sum & Two-Way Lookup (SUMPRODUCT)
Sum an entire dynamically matched row, sum an entire dynamically matched column, or retrieve a two-way matrix intersection across a 2D table using boolean-broadcast SUMPRODUCT.
Broadcast 1D header comparisons across a 2D budget matrix without volatile OFFSET!
Financial P&L cubes and regional quarterly tables store numbers in a 2D grid (B2:E5) with Region names down A2:A5 and Quarter headers across B1:E1. Analysts constantly need three dynamic summaries driven by dropdown selectors: (1) total across ALL quarters for a selected Region, (2) total across ALL regions for a selected Quarter, and (3) the exact 2D intersection cell—even summing duplicate department rows automatically if a label repeats!
Multiplying a vertical boolean vector (A2:A5 = G2) or horizontal boolean vector (B1:E1 = G3) directly against the 2D numeric block B2:E5 inside SUMPRODUCT broadcasts cleanly across every column and row! Look at our 4-region x 4-quarter revenue grid: selecting "South" sums 200 + 210 + 240 + 250 = 900 across Q1–Q4, selecting "Q3" sums 180 + 240 + 160 + 190 = 770 across all four regions, and intersecting both returns 240!
| Dynamic Selector Query | Boolean Broadcast Expression | Evaluated Slice | Result ($k) |
|---|---|---|---|
| Full Row Sum (Region G2 = "South") | (A2:A5 = G2) * B2:E5 | 200 + 210 + 240 + 250 | 900 |
| Full Column Sum (Quarter G3 = "Q3") | (B1:E1 = G3) * B2:E5 | 180 + 240 + 160 + 190 | 770 |
| 2D Intersection ("South" & "Q3") | (A2:A5 = G2) * (B1:E1 = G3) * B2:E5 | Single Cell D3 | 240 |
Dynamic Full-Row Matrix Sum = SUMPRODUCT((Row_Headers = Target) * Matrix_2D)
Sums every column in B2:E5 for rows matching selector G2, guarding unrecognized region names.
=IF(COUNTIF(A2:A5, G2) = 0, "Unknown Row Label", SUMPRODUCT((A2:A5 = G2) * B2:E5))Dynamic Full-Column Matrix Sum = SUMPRODUCT((Col_Headers = Target) * Matrix_2D)
Sums every row in B2:E5 for the column header matching selector G3.
=IF(COUNTIF(B1:E1, G3) = 0, "Unknown Column Header", SUMPRODUCT((B1:E1 = G3) * B2:E5))Try the example
Use the original values or change them to test your understanding.