Lookup & Reference
Count, sum and find the maximum in a selected column with CHOOSECOLS
Select a metric by its unique header, then use SUMPRODUCT to count qualifying rows or total a region and MAX for all regions.
Choose a column by header, then aggregate its array
G2 selects a header from B1:D1; XMATCH finds its position and CHOOSECOLS returns that column as an array. This example uses SUMPRODUCT, which accepts arrays, rather than assuming that a calculated array is interchangeable with a worksheet range in COUNTIFS or SUMIFS.
Keep headers unique and move headers together with their data. These fixed ranges do not automatically include new records or every inserted column. The example validates all twelve numeric metric cells and four region labels before calculating; a missing value is not silently treated as zero.
F2 matches a literal region label, without wildcard criteria. An unknown region produces a count and sum of zero. G2 must match exactly one nonblank header. A dropdown is optional and must be configured separately; typing a header works here.
| Region (A) | Q1 (B) | Q2 (C) | Q3 (D) |
|---|---|---|---|
| West | 120 | 90 (< 100) | 150 |
| East | 80 | 130 | 110 |
| West | 140 | 115 (>= 100) | 95 |
| West | 70 | 105 (>= 100 -> 2 hits, Sum = 310) | 160 |
Count the selected region’s values at least 100
For West and Q2, rows 4 and 5 qualify: the result is 2. Each Boolean array is coerced to numbers by multiplication.
=IF(OR(COUNT(B2:D5)<>12,COUNTBLANK(A2:A5)>0,COUNTBLANK(F2:G2)>0,SUMPRODUCT((B1:D1=G2)*1)<>1),"Invalid inputs",SUMPRODUCT((A2:A5=F2)*(CHOOSECOLS(B2:D5,XMATCH(G2,B1:D1,0))>=100)))Sum the selected region’s column
West totals 310 in Q2 and 405 in Q3. The count and sum use two equally sized four-row arrays.
=IF(OR(COUNT(B2:D5)<>12,COUNTBLANK(A2:A5)>0,COUNTBLANK(F2:G2)>0,SUMPRODUCT((B1:D1=G2)*1)<>1),"Invalid inputs",SUMPRODUCT((A2:A5=F2)*CHOOSECOLS(B2:D5,XMATCH(G2,B1:D1,0))))Maximum across all regions
This maximum is deliberately not filtered by F2: Q2 has a maximum of 130, belonging to East.
=IF(OR(COUNT(B2:D5)<>12,COUNTBLANK(A2:A5)>0,COUNTBLANK(F2:G2)>0,SUMPRODUCT((B1:D1=G2)*1)<>1),"Invalid inputs",MAX(CHOOSECOLS(B2:D5,XMATCH(G2,B1:D1,0))))Try the example
Use the original values or change them to test your understanding.