Scan column names, pick the ones containing specific text, and run functions on them (SUM, MAX etc)

Question

In Google Sheets, I have a spreadsheet that looks like this

A1    A2    A3    B1    B2    B3    C1    C2    C3
55    23    21    15    18    15    61    51    51
51    15    15    81    98    13    51    74    65
15    87    89    99    32    72    15    58    51
18    64    15    81    32    15    81    78    98
87    81    32    51    15    35    15    81    12

I want to create a separate sheet that will find the sum/max/average/min of all the "A" named columns.

Is there a way to make a function in a cell that will (lets' pick one for now) average all the cells in the same row whose column names match a certain text pattern (e.g. "A*")?

Tom Sharpe Tom Sharpe · Accepted Answer · 2019-06-16T12:13:57

You can use an array formula like this:

ArrayFormula(average(if(left(A1:I1,1)="A",A2:I)))

Or if preferred use pattern matching for more flexibility:

=ArrayFormula(average(if(regexmatch(A1:I1,"^A"),A2:I)))

(case sensitive).

Scan column names, pick the ones containing specific text, and run functions on them (SUM, MAX etc)

2 Answers