How do I reference an outside cell in a query?

Question

For budgeting, I'd like to be able to know how much money was due before the next paycheck. I was able to figure out the paycheck date formula, but I'm having an issue with the query. It looks like it is referencing the correct cell, but it is giving me an #ERROR still. Any help would be appreciated.

=QUERY(G2:H14,"select sum(G) where H<"B16"")

Here is a link to a sample sheet. I'm specifically looking for the formula that would go in G17.

https://docs.google.com/spreadsheets/d/14PMWNXfHIpEhoQD_xJ52s4exhN7Xd_ke5yPGxuTFLyQ/edit?usp=sharing

Answer 1

You can try this formula for your desired output

Formula:

=QUERY(G2:H14, "SELECT SUM(G) WHERE H < date '"&TEXT(B16, "yyyy-mm-dd")&"' label SUM(G) ''", 0)

Output:

References:

Query function

Query with dates

Answer 2

You can use dates with QUERY, but it's tricky. I suggest to use SUMIF:

=SUMIF(H2:H14,">"&B16,G2:G14)