Is there a way to make a google query sum multiple columns scalable?-CodePudding

Right now I use the function below to sum the quantities in each column shown in the image below. Occasionally I need to add more columns, and I might even need as much as 50 but I don't want to have a super long query formula. Is there a way to condense it and write it to be scalable to the amount of columns I require?

Function: =ARRAYFORMULA(IF(ISBLANK(AN4:AN), "", TRANSPOSE(QUERY({VALUE(L4:AM)}, "SELECT sum(Col1), sum(Col2), sum(Col3), sum(Col4), sum(Col5), sum(Col6), sum(Col7), sum(Col8), sum(Col9), sum(Col10), sum(Col11), sum(Col12), sum(Col13), sum(Col14), sum(Col15), sum(Col16), sum(Col17), sum(Col18), sum(Col19), sum(Col20), sum(Col21), sum(Col22), sum(Col23), sum(Col24), sum(Col25), sum(Col26), sum(Col27), sum(Col28) LABEL sum(Col1) '', sum(Col2) '', sum(Col3) '', sum(Col4) '', sum(Col5) '', sum(Col6) '', sum(Col7) '', sum(Col8) '', sum(Col9) '', sum(Col10) '', sum(Col11) '', sum(Col12) '', sum(Col13) '', sum(Col14) '', sum(Col15) '', sum(Col16) '', sum(Col17) '', sum(Col18) '', sum(Col19) '', sum(Col20) '', sum(Col21) '', sum(Col22) '', sum(Col23) '', sum(Col24) '', sum(Col25) '', sum(Col26) '', sum(Col27) '', sum(Col28) ''"))))

Picture below is with the cell selected with function, and the output. You can see the range it sums up the columns for.

CodePudding user response：

yes:

=INDEX(IF(AN4:AN="",,FLATTEN(QUERY(QUERY({VALUE(INDIRECT("L4:"&ROWS(L4:L)))}, 
 "select "&JOIN(",", " sum(Col"&SEQUENCE(30)&")")), "offset 1", ))))

to add more columns change 30 to something else

circular dependency is from INDIRECT("L4:"&ROWS(L4:L))

you can either go with a flow and enable iterations

or use something like:

=INDEX(IF(AN4:AN="",,FLATTEN(QUERY(QUERY({VALUE(
 INDIRECT("L4:"&ADDRESS(ROWS(L4:L), COLUMN()-1, 4)))}, 
 "select "&JOIN(",", " sum(Col"&SEQUENCE(30)&")")), "offset 1", ))))