Lookup & Dynamic Arrays
Conditional Count with a Dynamic Selectable Column (CHOOSECOLS & MATCH)
Count rows meeting both a row category filter and a threshold on a user-selected metric column using CHOOSECOLS, MATCH, and SUMPRODUCT without hardcoding separate formulas per quarter.
Switch the evaluated KPI column dynamically from a header selector cell
Quarterly scorecards store Region in column A and separate quarterly SLA scores in columns B:D (Q1, Q2, Q3). Instead of rewriting COUNTIFS every quarter, analysts pick the active quarter name in selector cell B9 and let MATCH(B9, B1:D1, 0) locate the column index.
Extracting that active column vector via CHOOSECOLS(B2:D7, MATCH(B9, B1:D1, 0)) inside SUMPRODUCT(--(A2:A7 = B8), --(selectedCol >= B10)) evaluates the conditional count cleanly and updates automatically when the user switches quarters or thresholds.
| Region | Q1 | Q2 | Q3 | Matches North & Q2 >= 90? |
|---|---|---|---|---|
| North | 86 | 93 | 91 | Yes (1) |
| South | 94 | 88 | 95 | Region <> North |
| North | 91 | 84 | 92 | Q2 < 90 |
| North | 89 | 95 | 96 | Yes (2) |
| West | 92 | 91 | 89 | Region <> North |
| North | 78 | 90 | 85 | Yes (3 — exact boundary) |
Dynamic-Column Conditional Count
Matches selector header B9 against B1:D1, guards unmatched period names via ISNA, slices that column via CHOOSECOLS, and counts rows in region B8 >= threshold B10.
=LET(colIdx, MATCH(B9, B1:D1, 0), IF(ISNA(colIdx), "Unknown Period", SUMPRODUCT(--(A2:A7 = B8), --(CHOOSECOLS(B2:D7, colIdx) >= B10))))Dynamic-Column Conditional Sum for Selected Quarter
Totals all scores in the selected quarter column for the chosen region.
=LET(colIdx, MATCH(B9, B1:D1, 0), IF(ISNA(colIdx), "Unknown Period", SUMPRODUCT(--(A2:A7 = B8), CHOOSECOLS(B2:D7, colIdx))))Try the example
Use the original values or change them to test your understanding.