Home > Software design >  How can I prevent QUERY from printing its calculation to two rows, and not have it print "sum&q
How can I prevent QUERY from printing its calculation to two rows, and not have it print "sum&q

Time:09-03

I've created a Google Sheets function, with the intended effect of getting the sum of numeric values between two dates, where the value in a different column is equal to a string. The calculation however is having unintended side-effects.

The problem is that the function starts out by returning the string value "sum" to the cell, before returning the actual result of the calculation onto a second row.

How can I limit the QUERY function in Google Sheets to only return the value of the calculation I'm querying for?

=QUERY('Finance, raw data'!A2:D;"SELECT SUM(C) WHERE D='Audible' AND A >= date '2021-08-01' AND A < date '2021-09-01'")

CodePudding user response:

label it:

=QUERY('Finance, raw data'!A2:D;
 "select sum(C) 
  where D  ='Audible' 
    and A >= date '2021-08-01' 
    and A <  date '2021-09-01'
  label sum(C)''")

offset it:

=QUERY(QUERY('Finance, raw data'!A2:D;
 "select sum(C) 
  where D  ='Audible' 
    and A >= date '2021-08-01' 
    and A <  date '2021-09-01'"); 
 "offset 1"; 0)

index it:

=INDEX(QUERY('Finance, raw data'!A2:D;
 "select sum(C) 
  where D  ='Audible' 
    and A >= date '2021-08-01' 
    and A <  date '2021-09-01'
  label sum(C)''"); 2)
  • Related