Google Sheets: How to use arrayformula to find and get data from only a certain, dynamic set of columns

0
votes

I have a Google sheets table where my headers are dynamically generated from another source, and can show up with any header values, in any order.

In the link example I've got the header values: AA - BB - CC - ABBA - KK - VV but it could just has well been: CC - AA - QQ - YY

I don't know in advance which headers I will get, or how many.

My issue is that I want to sum each product values, per row, using an arrayformula, where the column headers include for instance "BB".

So basically =arrayformula(sumifs(G4:L1000,$G$3:$L$3,"BB")), if that had been a thing.

My closest guess is that I need to use a VLOOKUP of some sort, but since the index of the columns to get the data from isn't static I can't quite figure out how to solve it.

Desperate for help.

https://docs.google.com/spreadsheets/d/1ZcMldTJrFLz_f_w-lGMIuAKs_yGSc1eDXbA2tgr9Bw4/edit?usp=sharing

I can get arrays of all the headers that match my query (in B4:B): 

=TRANSPOSE(SPLIT(arrayformula(CONCATENATE(IF(REGEXMATCH(G3:L3,"BB"),G3:L3&"~",""))),"~"))

and then get the column indexes (in C4:C):

=iferror(arrayformula(match(B4:B,$G$3:$L$3,0)+6),"")

or even column letters (in D4:D):

=iferror(arrayformula(REGEXEXTRACT(ADDRESS(ROW(), indirect("C4:C"&counta(B4:B)+3)), "[A-Z]+")),"")

and then I had some half baked idea of using this in the vlookup, but now I'm just completely and utterly stuck.

1
sumifsarray-formulasgoogle-sheets-formulags-vlookup

1 Answers

0
votes

You could create an intermediate table using

=filter(F3:L, arrayformula(regexmatch(F3:L3,"BB")))

and then use SUM on each resulting column to get what you need. If the FILTER results begin on row 2, then you can place the SUM functions on row 1 so they won't get in the way of the FILTER expansion.