Search code examples
dategoogle-sheetstime-seriesgoogle-sheets-formulagoogle-query-language

Sum values associated with a date equal to or less than today in Google Sheets

So I have a table that looks like this:

date goal
10/1/2022 10000
10/2/2022 10000
10/3/2022 10000
10/4/2022 10000
10/5/2022 10000
10/6/2022 10000

I would like to create a formula that Sums the goal column for the dates less than or equal to the current day. I currently have this:

=query(A4:B1000, "select SUM(B) WHERE A <= today()")

But this is throwing the following error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 31. Was expecting one of: <EOF> "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and"

Any thoughts on how to proceed would be helpful. Thanks!

Solution

  • This is a typical use case for SUMIF, which will sum values in a range that meet a condition (a condition on that same range, or on a corresponding range):

    =sumif(A4:A1000,"<="&TODAY(),B4:B1000)

    will sum the values in B4:B1000 for which the corresponding value in A4:A1000 is less than or equal to today.