Google Sheets Query Sum Multiple Columns automatically

Question

I have a spreadsheet with dynamic columns. I'd like to sum the columns by adding a row. I have found how to sum rows with Query but not columns.

My queries are below and I want to add at the end of each of them the sum of the columns.

=
{
INDEX(QUERY(QUERY({Staffs!A2:1000}, 
 "select Col1,"&TEXTJOIN(",", 1, 
 "sum(Col"&SEQUENCE(COUNTA(Staffs!B1:1), 1, 2)&")")&"
  where Col1 is not null 
  group by Col1"), 
 "offset 1", 0));
{" ",ArrayFormula(EDATE(eomonth(Salaries!L1,0),SEQUENCE(1,7*12-MONTH(Salaries!L1)+1,0,1)))};
INDEX(QUERY(QUERY({Salaries!N2:1000}, 
 "select Col1,"&TEXTJOIN(",", 1, 
 "sum(Col"&SEQUENCE(COUNT(TRANSPOSE(Salaries!O2:2)), 1, 2)&")")&"
  where Col1 is not null 
  group by Col1"), 
 "ORDER BY Col1 ASC offset 1", 0))
}

The spreadsheet can be viewed there: https://docs.google.com/spreadsheets/d/1veiYh1CMIfFPwBGQk4OwKmCLa7q08TugReVfAXtpIgI/edit#gid=280688035

Basically I want to calculate the sum for each month for the number of staffs and the salaries

Thank you!

How about putting your sum in the first left column before the query just as a SUM(B3:3) for example instead of trying to put it inside the query? :-) Having a sum on the left makes it also easier for browsing. — Krzysztof Dołęgowski
Thanks for the suggestion Krzysztof Dołęgowski. The idea is to have the sum for each column so it needs to be in the query ;) — Jey Jay

Krzysztof Dołęgowski Krzysztof Dołęgowski · Accepted Answer · 2021-03-06T18:07:58

I start with a summary. Here is a general formula that adds row to any table with numbers and strings on the left (like yours). Then I explains how I got it.

I use name TABLE but it might be any formula that builds a table (query, arrayformula, filter, sequence you name it).

Summary for pure numeric table:

={TABLE;index(query({TABLE},"Select "&textjoin(",",1,INDEX("sum(Col"&sequence(1,columns(TABLE),1)&")"))),2)}

Summary for table with first 1 column with text

={TABLE;"",index(query({TABLE},"Select "&textjoin(",",1,INDEX("sum(Col"&sequence(1,columns(TABLE)-1,2)&")"))),2)}

Summary for table with first 2 columns with text

={TABLE;"","",index(query({TABLE},"Select "&textjoin(",",1,INDEX("sum(Col"&sequence(1,columns(TABLE)-2,3)&")"))),2)}

etc.

I've made a first half of the job and I explain how to do the next part. Actually I use the same method as you :-)

First I count columns of the query:

=columns(your query)

Then I use it to build an expression that is used in new query. Generally we need. =query(your query,"select sum(Col1), sum(Col2)... up to calculated number of columns")

The formula looks like this:

=textjoin(",",1,INDEX("sum(Col"&sequence(1,columns(YOUR QUERY HERE)-1,2)&")"))

Then I put it under your query using curly brackets.

{your query; "Sum of month",query(your query,"select sum(Col1), sum(Col2)... up to calculated number of columns")}

Here is my solution: https://docs.google.com/spreadsheets/d/1WDVM6WVd-JZrLOerysBR0mVzLvBsAKy5ILB58g7rKKY/edit?usp=sharing

Google Sheets Query Sum Multiple Columns automatically

1 Answers