Auto update date in QUERY function

Question

I have the following, working Google Sheets formula:

=IFERROR(QUERY(Transactions!A25:P990030,"select Sum(H) where B = date '2020-01-01' and F contains 'Product Sales' and K contains 'United States' and L contains 'Brand' label Sum(H) ''",0),"-")

I would like the date section, where B = date '2020-01-01', to auto-update in new cells, so it fills the 365 cells with the correct dates. However, I can't get it to work.

Column B also has the corresponding date, so B21 is equal to January 1st, 2020, and B22 to Jan 2, etc. However, it seems that the QUERY function and dates are tricky, as changing the date from '2020-01-01' to "2020-01-01" already turns into an error.

Example/test sheet: https://docs.google.com/spreadsheets/d/1uYWzfgmeiP4JEKM5sMzbxfL2DCKLtfMiK5u_pnIbQY8/edit?usp=sharing

To understand how to filter by date columns with QUERY, read this blog: benlcollins.com/spreadsheets/query-dates You are right that they are not treated the same as simple strings or numberic values. — kirkg13
@Player0 My bad, removed some info but preserved all needed for the formula: docs.google.com/spreadsheets/d/… — Nuxurious
@kirkg13 I've read it, the only way the formula worked is with the where B = date '2020-01-01' however, the problem is that it doesn't auto update if I drag the cells down. I want to have the formula for the entire year, with date being Jan 2, 3, 4, to Dec 31. — Nuxurious
It's no longer needed, since @player0 has already provided an even better formula, but I believe this version of his original formula may work as you expect: =QUERY(Transactions!A$25:P, "select sum(H) where B = date '"& text("2020-01-01"+ROW(A1)-1,"yyyy-mm-dd")&"' and F contains 'Product Sales' and K contains 'United States' and L contains 'Brand' label sum(H)''", 0) Note the use of: TEXT("date string"+number,"yyy-mm-dd") This worked for me. — kirkg13

player0 player0 · Accepted Answer · 2020-12-26T16:08:10

use:

=ARRAYFORMULA(IFNA(VLOOKUP(B21:B, QUERY(Transactions!A:P,
 "select B,sum(H)  
  where F contains 'Product Sales' 
    and K contains 'United States' 
    and L contains 'Brand'
  group by B 
  label sum(H)''", 0), 2, 0), "-"))

Auto update date in QUERY function

1 Answers