CODEWITHSUNDEEP
google-sheets
JP Douma
JP Douma

Reputation: 135

Adding a SUM row and SUM column to a query in Google Sheets

I have pulled data from a time sheet using QUERY (see code). I would like to add the totals per row and per column i.e. replacing "2", "3", "4" ... with a sum formula for totals per column. And somehow add a line of code for the totals per row as well. I have added the link to a copy of the spreadsheet ... sheet Hour Summary. https://docs.google.com/spreadsheets/d/1czL8ttM-0Z7wPInkzOTQA8w0B24TiqC9OP-2rFjD2Dc/edit?usp=sharing

I have tried to sum from the source dataset adding a QUERY with a WHERE clause. Except I am not clear what to filter on in this case. I am new to this, so not yet fully in command of all the syntax. Somehow I can't seem to figure out the last leg of this problem. Help is appreciated.

={query(query(formData, "select E, C, sum(H) where C != '' group by E, C", 1), "select Col1, Sum(Col3) group by Col1 Pivot Col2 label Col1'Month'");{"Total","2","3","4","5","6","7","8","9","10","11","12"}}

Upvotes: 0

Views: 3548

Answers (3)

DonatasM
DonatasM

Reputation: 28

A simpler way to dynamically add a summary row and summary column to query results using new Google Sheets functions BYROW and BYCOL. You only need to write the query once.

=BYROW(
  BYCOL(
   QUERY(formData, 
    "select D, sum(H) 
     where C != ''
     and D is not null 
     group by D pivot C ", 1)
   ,LAMBDA(cv, {cv;IF(count(cv)=0,"TotByName", SUM(cv))}))
  ,LAMBDA(rv, {rv,IF(count(rv)=0,"TotByMonth", SUM(rv))}))

Result

Upvotes: 1

player0
player0

Reputation: 1

={{QUERY(QUERY(formData, 
  "select E, C, sum(H) 
   where C is not null 
   group by E, C", 1), 
  "select Col1, sum(Col3) 
   group by Col1 
   pivot Col2 
   label Col1'Month'");
 {QUERY(QUERY(QUERY(QUERY(formData, 
  "select E, C, sum(H) 
   where C is not null 
   group by E, C", 1), 
  "select Col1, sum(Col3) 
   group by Col1 
   pivot Col2 label Col1''"),
  "select 'x',Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12"),
  "select 'Total',sum(Col2),sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8),sum(Col9),sum(Col10),sum(Col11),sum(Col12) 
   group by Col1 
   label 'Total''',sum(Col2)'',sum(Col3)'',sum(Col4)'',sum(Col5)'',sum(Col6)'',sum(Col7)'',sum(Col8)'',sum(Col9)'',sum(Col10)'',sum(Col11)'',sum(Col12)''")}},
 ARRAYFORMULA(QUERY(VALUE({QUERY(QUERY(formData, 
  "select E, C, sum(H) 
   where C is not null 
   group by E, C", 1), 
  "select Col1, sum(Col3) 
   group by Col1 
   pivot Col2 
   label Col1'Month'");
 {QUERY(QUERY(QUERY(QUERY(formData, 
  "select E, C, sum(H) 
   where C is not null 
   group by E, C", 1), 
  "select Col1, sum(Col3) 
   group by Col1 
   pivot Col2 label Col1''"),
  "select 'x',Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12"),
  "select 'Total',sum(Col2),sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8),sum(Col9),sum(Col10),sum(Col11),sum(Col12) 
   group by Col1 
   label 'Total''',sum(Col2)'',sum(Col3)'',sum(Col4)'',sum(Col5)'',sum(Col6)'',sum(Col7)'',sum(Col8)'',sum(Col9)'',sum(Col10)'',sum(Col11)'',sum(Col12)''")}}),
  "select Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9+Col10+Col11+Col12
   label Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9+Col10+Col11+Col12'Total'"))}

0

Upvotes: 3

player0
player0

Reputation: 1

={QUERY(QUERY(formData, 
  "select E, C, sum(H) 
   where C != '' 
   group by E, C", 1), 
  "select Col1, sum(Col3) 
   group by Col1 
   pivot Col2 
   label Col1'Month'");
 {QUERY(QUERY(QUERY(QUERY(formData, 
  "select E, C, sum(H) 
   where C != '' 
   group by E, C", 1), 
  "select Col1, sum(Col3) 
   group by Col1 
   pivot Col2 label Col1''"),
  "select 'x',Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12"),
  "select 'Total',sum(Col2),sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8),sum(Col9),sum(Col10),sum(Col11),sum(Col12) 
   group by Col1 
   label 'Total''',sum(Col2)'',sum(Col3)'',sum(Col4)'',sum(Col5)'',sum(Col6)'',sum(Col7)'',sum(Col8)'',sum(Col9)'',sum(Col10)'',sum(Col11)'',sum(Col12)''")}}

0

Upvotes: 0

Related Questions