Search code examples
filtergoogle-sheetsarray-formulasgoogle-sheets-querycrystal-reports-formulas

How do I add a subtotal to one of the columns of my query in Google Sheets and offset it to a near by cell?

I want to offset the total of a result column of a query in Google Sheets.

I have tried googling but that doesn't help. My query is linked to date drop-down. The size of the query result depends upon the date selected. I want a total sum and total count of say column 2 of the query result to be shown to the left of the last row of the query result.

Solution 
  • =ARRAYFORMULA({QUERY(A1:B, "where A >= date '"&TEXT(A1, "yyyy-MM-dd")&"' 
                              and A <= date '"&TEXT(A4, "yyyy-MM-dd")&"'"),
 {SPLIT(TRANSPOSE(SPLIT(REPT("♥ ♦ ♦", 
  COUNTA(FILTER(A1:A, A1:A>=A1,A1:A<=A4))-1),"♥")),"♦"); 
  "Total Sum:", SUM(QUERY(A1:B, "select B where A >= date '"&TEXT(A1, "yyyy-MM-dd")&"' 
                                            and A <= date '"&TEXT(A4, "yyyy-MM-dd")&"'"))}})

    0

    =ARRAYFORMULA({QUERY({VALUE(SUBSTITUTE('All payments'!D2:D, "₹",)), 'All payments'!A2:M, 
 DATEVALUE('All payments'!E2:E), 'All payments'!A2:A, 'All payments'!Y2:Y}, 
 "select ' ', Col2, SUM(Col1), Col17, Col13, Col14, Col3, Col5, Col16 
  where Col15 >= "&DATEVALUE(F1)&" 
    and Col15 <= "&DATEVALUE(F2)&" 
  group by Col2, Col17, Col13, Col14, Col3, Col5, Col16 label SUM(Col1)'', ' '''", 0);
 QUERY(QUERY(QUERY('All payments'!A2:M, R1), Q1, 0), "select sum(Col2) label sum(Col2)''"),
 "","","","","","","",""})

    0