Statistical
Dynamic two-way average by row category and column header (CHOOSECOLS + FILTER)
Average rows matching a region or department while dynamically switching which quarter or metric column is aggregated via XMATCH + CHOOSECOLS.
Aggregate a user-selected metric column without rewriting AVERAGEIFS ranges
To average by region and a selected metric, locate the selected header, extract that column, then filter its rows. This is one approach; AVERAGEIFS can also be combined with dynamic range selection.
Combining XMATCH(SelectedPeriod, B1:D1) with CHOOSECOLS(B2:D6, colIdx) extracts the exact metric column dynamically, and FILTER(..., A2:A6 = SelectedRegion) isolates the matching rows before AVERAGE computes the mean!
| Region (A) | Q1 (B) | Q2 (C) | Q3 (D) |
|---|---|---|---|
| West (Row 2) | 120 | 150 | 180 |
| East (Row 3) | 90 | 110 | 130 |
| West (Row 4) | 180 | 210 | 240 |
| East (Row 5) | 110 | 130 | 150 |
| West (Row 6) | 150 | 180 (West Q2 Avg = 180) | 210 |
Dynamic two-way conditional average
F2 specifies the row category ("West") and G2 specifies the column header ("Q2").
Headers must be unique. Numeric selected values are included, zero counts, and blank or text values are excluded. Unknown headers and groups without numeric values receive explicit messages.
=IF(COUNTIF(B1:D1,G2)<>1,"Choose a unique header",LET(values,CHOOSECOLS(B2:D6,XMATCH(G2,B1:D1)),mask,(A2:A6=F2)*ISNUMBER(values),IF(SUM(mask)=0,"No numeric observations",AVERAGE(FILTER(values,mask)))))Dynamic two-way conditional sum using the same pattern
Swapping AVERAGE for SUM aggregates total volume for the selected Region × Quarter intersection.
=IF(COUNTIF(B1:D1,G2)<>1,"Choose a unique header",LET(values,CHOOSECOLS(B2:D6,XMATCH(G2,B1:D1)),mask,(A2:A6=F2)*ISNUMBER(values),IF(SUM(mask)=0,"No numeric observations",SUM(FILTER(values,mask)))))Try the example
Use the original values or change them to test your understanding.