google-sheetssumgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Use today's year/month/week as reference in Google query language

I'm trying to sum a column of data, payments (accounting) relative to the date of receipt in another column. I want to isolate payments made in a given year, month, or week, without having to change anything in the query code.

This is roughly what I'm working with, trying to use either TODAY() or NOW() in order to have it automatically update:

=query(amount,"select sum(H) where year(F) = year(today())")

I've also tried doing something similar, while referencing a cell that would read today's year, but have had difficulties with this:

=query(amount,"select sum(H) where year(F) = '"&text(R1,"yyyy-MM-dd")&"'")

where R1 is a cell reading =YEAR(TODAY))

If I can get the first formula to work with both year, month, and also week, that'd be perfect.

Solution

  • it can be done but you will need to add a few more parameters to the query...

    =SUM(IFERROR(QUERY(A1:B, 
 "select sum(B) 
  where year(A)="&YEAR(TODAY())&"
  group by A 
  label sum(B)''", 0)))

    0