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

## 14 February 2022 - 1 answer

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.

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(