I have a doubt regarding a change in this formulas in sheets:

=INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2)

=INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");0)

Formula number 1 works fine, but when I add 'and' to it (formula 2), I cannot use "2" as index value, and when I use 0 or 1 the result shown in the cell is "Sum"....(k15 cell is a date: 31/01/2018)

Any clue why this could happen?

  • Hi Jean-Pierre, thanks again for the solution.Working around it, I've found a problem, the formula does not allow me to insert any left columns or paste it into other cells....Sure I'm doing something wrong... – Odraude Feb 22 '18 at 7:48
  • 1
    Possible duplicate of Dynamic date range query – Rubén Oct 4 '18 at 21:24

INDEX use 1 based indexes, meaning that the indexes start on 1. By the other hand to make QUERY return no column headers use use the clause label to set the column label to empty strings. Example:

=INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"' label sum (F) ''");0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.