Ad

Is There A Way To Make A Google Query Sum Multiple Columns Scalable?

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.Image of the original function in action.

Ad

Answer

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

enter image description here

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", ))))
Ad
source: stackoverflow.com
Ad