Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querytextjoin

Google Sheets Query sum indeterminate number of columns

select sum(Col1),sum(Col2),sum(Col3) ... up to 500 columns but the number might increase or decrease... what is the best way to go about this with one formula?

it would be really nice if you could just do select sum(*) ... but sadly that does not seem to work.

Solution

  • you can do:

    =ARRAYFORMULA(QUERY({A1:SF}, "select "&TEXTJOIN(",", 1, "sum(Col"&ROW(A1:A500)&")")))

    0

    or:

    =ARRAYFORMULA(QUERY({A1:SF}, "select "&TEXTJOIN(",", 1, "sum(Col"&COLUMN(A1:SF)&")")))

    or infinitely expanding:

    =ARRAYFORMULA(QUERY({INDIRECT("A1:"&SUBSTITUTE(ADDRESS(1, COLUMNS(1:1), 4), 1, ))}, 
 "select "&TEXTJOIN(",", 1, "sum(Col"&ROW(INDIRECT("A1:A"&COLUMNS(1:1)))&")")))