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

Question

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!

Yes but you have to be very careful comparing now (a datetime) to an actual date. In my opinion use of today() is better. — Tom Sharpe, Oct 05 '22 at 20:17
@TomSharpe How so? At most it'll include today's 23:59 hours in the inclusion criteria. I think doing it inside query is still much better option. — TheMaster, Oct 05 '22 at 20:18
It works in this particular case but =query(A2:B1000, "select SUM(B) WHERE A >= now()") doesn't select today's date for example. — Tom Sharpe, Oct 05 '22 at 20:34
@TomSharpe I see. I'll still prefer doing it inside query: `=query(A2:B1000, "select SUM(B) WHERE A >= todate(now())")` — TheMaster, Oct 05 '22 at 21:09

score 3 · Accepted Answer · answered Oct 05 '22 at 13:56

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.

score 0 · Answer 2 · answered Oct 05 '22 at 14:25

Or you can still use a query if you prefer as long as you get the right syntax - see this for example.

=query(A2:B1000, "select SUM(B) WHERE A <= date '"& text(today(),"yyyy-mm-dd")&"'")

Dates in my locale are in dd/mm/yyyy format so 6/10/2022 is tomorrow at time of writing.

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

2 Answers2