SUMIFS Between Two Dates in Google Sheets (and the Last Day It Misses)

To add up the values that fall between two dates, give SUMIFS two conditions on the date column: one for the start and one for the end. The formula is short, but in our tests the most common version missed a January sale worth 200, with no error. This guide shows the version that doesn’t, and the other things that quietly change the total.

The examples use a list of orders, with dates in A2:A13 and amounts in E2:E13:

Row A: Date C: Status E: Amount
2 2026-01-05 Paid 120
3 2026-01-12 Pending 80
4 2026-01-20 Pending 45
5 2026-01-31 18:30 Paid 200
6 2026-02-03 Paid 60
7 2026-02-14 Overdue 150
8 2026-02-28 Paid 90
9 2026-03-01, typed as text Paid 300
10 2026-03-15 Cancelled 75
11 2026-03-20 Pending 110
12 2026-03-31 Paid 100, typed as text
13 (empty) Paid 50

January has four orders: 120 + 80 + 45 + 200 = 445.

Tested in Google Sheets. Every formula on this page was run on November 12, 2026, and the results below are the ones Google Sheets returned. How we test

The formula

=SUMIFS(E2:E13, A2:A13, ">="&DATE(2026,1,1), A2:A13, "<"&DATE(2026,2,1))

This returned 445. Read it as: add up E where the date is on or after January 1 and before February 1.

  • The sum range comes first in SUMIFS, then the pairs of range and condition.
  • The operator goes in quotes and the date is joined with &. DATE(year, month, day) means the same date in any locale.
  • The end condition is “before the next day”, not “on or before the last day”. The next section explains why.

Why “<=” the end date missed a sale

End condition Result
"<="&DATE(2026,1,31) 245
"<"&DATE(2026,2,1) 445

Found in testing: the order in row 5 has a date and a time, January 31 at 18:30. A date with a time is a slightly larger number than the date alone, so “on or before January 31” left it out. The total dropped from 445 to 245 with no warning. “Before February 1” includes every moment of January 31.

Dates imported from forms, apps and timestamps usually carry a time even when the cell only shows the date. Using “<” with the day after the end date is safe either way. With the end date in a cell, add 1: "<"&L4+1.

Start and end dates in cells

With 2026-01-01 in L3 and 2026-01-31 in L4:

=SUMIFS(E2:E13, A2:A13, ">="&L3, A2:A13, "<"&L4+1)

returned 445. With the references inside the quotes, ">=L3" and "<=L4", the result was 0, with no error.

A whole month with EOMONTH

EOMONTH(date, 0) returns the last day of that date’s month, so you only need to type the first day:

=SUMIFS(E2:E13, A2:A13, ">="&L3, A2:A13, "<"&EOMONTH(L3,0)+1)

This returned 445 for January. For February, ">="&DATE(2026,2,1) with "<="&EOMONTH(DATE(2026,2,1),0) returned 300 (60 + 150 + 90). That’s correct here, but only because no February order has a time. The “<” version with +1 is the safer habit.

The last 30 days

For a rolling period, compare against TODAY(): ">="&TODAY()-30 and "<="&TODAY(). Its result changes every day, so we tested the same formula with a fixed date, 2026-02-15, in L5:

=SUMIFS(E2:E13, A2:A13, ">="&L5-30, A2:A13, "<="&L5)

It returned 455: the orders from January 16 to February 15 (45 + 200 + 60 + 150).

Dates with another condition

Add more pairs. Paid orders in the first quarter:

=SUMIFS(E2:E13, A2:A13, ">="&DATE(2026,1,1), A2:A13, "<"&DATE(2026,4,1), C2:C13, "Paid")

returned 470. Conditions can include wildcards too: January orders whose item contains “apple” totaled 245. See COUNTIF contains text for how the wildcards work.

What SUMIFS leaves out without telling you

All the orders in column E add up to 1,280 with SUM. The first-quarter SUMIFS above, without the Paid condition, returned 930. The other 350 come from rows SUMIFS can’t place in a date range:

  • A date typed as text (row 9, 300). It looks like a date but isn’t one, so no date condition matches it. =SUMPRODUCT(--ISTEXT(A2:A13)) returned 1, which is a quick way to find such cells. Our guide to sorting dates stored as text shows how to convert them.
  • A row with no date (row 13, 50). To see what’s missing, =SUMIFS(E2:E13, A2:A13, "") returned 50.

The amount typed as text (row 12) is left out of every sum, SUM included: Ana’s paid orders, for example, totaled 420, not 520. See convert text to numbers.

Dates written inside the quotes

You can type a date straight into the condition, but how it’s read depends on the spreadsheet’s locale. In our sheet, set to United States:

Conditions Result
">=1/1/2026", "<=1/31/2026" 245 (the “<=” problem again)
">=2026-01-01", "<2026-02-01" 445
">="&TEXT(L3,"yyyy-mm-dd"), "<"&TEXT(L4+1,"yyyy-mm-dd") 445
">="&TEXT(L3,"dd/mm/yyyy"), "<"&TEXT(L4+1,"dd/mm/yyyy") 0

The day-first version returned 0. Its end date, February 1, became the text “01/02/2026”, and a US sheet reads that as January 2: =DATEVALUE("01/02/2026")=DATE(2026,1,2) returned TRUE. The range shrank to January 1 only, which has no orders. DATE() and cell references avoid the question entirely.

SUMIF puts the sum range last

With a single date condition you can use SUMIF, but its arguments come in a different order: range, condition, then the range to add.

Formula Result
=SUMIF(A2:A13, ">="&DATE(2026,3,1), E2:E13) 185 (75 + 110; the text amount on March 31 is skipped)
=SUMIF(E2:E13, ">="&DATE(2026,1,1), A2:A13), in SUMIFS order 0
=SUMIF(A2:A13, ">="&DATE(2026,3,1)), sum range left out 138309: the dates themselves added up

Neither mistake shows an error. If a total is 0 or absurdly large, check the argument order first.

The same total with SUMPRODUCT or QUERY

  • =SUMPRODUCT((A2:A13>=L3) * (A2:A13<=L4), E2:E13) returned 245. It has the same end-date problem; use A2:A13<L4+1.
  • =QUERY(A1:E13, "select sum(E) where A >= date '2026-01-01' and A < date '2026-02-01' label sum(E) ''", 1) returned 445. QUERY needs the word date before the date, as explained in our QUERY guide.

To count orders instead of adding them, swap in COUNTIFS with the same conditions. February had 3 orders. More on counting with several conditions is in COUNTIF with multiple criteria.

Related guides