Google Sheets query not working when referencing a cell-CodePudding

I use the query function to extract data from my tables in google sheets. But one of the conditions must be a reference to another cell. I do it like this:

=QUERY({$A2:$B,ArrayFormula(Month($C$2:$C)),$D2:D},"Select SUM(Col4) Where (Col3 = '"&H2&"' ) label sum(Col4) ''",0)

The above result is #N/A. (The field H2 above contains the number 10).

If I replace the '"&H2&"' with the actual number, like below, I get output:

=QUERY({$A2:$B,ArrayFormula(Month($C$2:$C)),$D2:D},"Select SUM(Col4) Where (Col3 = 10 ) label sum(Col4) ''",0)

Any help is appreciated. Thanks in advance.

CodePudding user response：

Single quotes are unnecessary in case of numbers:

=QUERY(
  {
    $A2:$B,
    ARRAYFORMULA(Month($C$2:$C)),
    $D2:D
  },
  "SELECT SUM(Col4)
   WHERE Col3 = " & H2 & "
   LABEL SUM(Col4) ''",
)

Or you could use MONTH inside QUERY:

=QUERY(
  {A:D},
  "SELECT SUM(Col4)
   WHERE MONTH(Col3) = " & H2 - 1 & "
   LABEL SUM(Col4) ''",
)

MONTH in QUERY is 0-based, so there is H2 - 1.