Excel guide · 3 min read
SUMIFS between two dates in Excel
=SUMIFS(D2:D9,A2:A9,">="&F2,A2:A9,"<="&G2), with the amounts in D, the dates in A, the start date in F2 and the end date in G2. The date column appears twice, once for each limit, and only rows inside both limits are added.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Region | Amount | Start | End | |
| 2 | Sep 29, 2026 | Halvorsen Ltd | North | 1250 | Oct 1, 2026 | Oct 31, 2026 | |
| 3 | Oct 1, 2026 | Brightwater Co | South | 860 | |||
| 4 | Oct 8, 2026 | Okafor & Sons | North | 2140 | |||
| 5 | Oct 14, 2026 | Brightwater Co | South | 495 | |||
| 6 | Oct 21, 2026 | Lindqvist AB | North | 1780 | |||
| 7 | Oct 31, 2026 | Halvorsen Ltd | South | 930 | |||
| 8 | Nov 2, 2026 | Okafor & Sons | North | 615 | |||
| 9 | 10/17/2026 | Lindqvist AB | South | 1100 |
Download this sheet as an Excel practice file (.xlsx)
Sum between a start and end date
SUMIFS takes the range to add first, then pairs of range and condition. The operator goes in quotes and is joined to the date cell with &. Keeping the dates in cells lets you report any period by changing two cells.
Five October sales add up to 6,205. The Lindqvist sale on 10/17/2026 should make six; the last section explains why it is missing.
Result: 6205
6,205: sales from October 1 to October 31, both days included.
Add a region, customer or status
Every extra condition is another range and criterion pair. Each one must be true for a row to be added, so this sums North region sales in October.
To total several regions, add one SUMIFS per region, or point the region condition at a cell so a dropdown can switch it.
Result: 3920
3,920: Okafor & Sons (2,140) and Lindqvist AB (1,780) in October.
A whole month from any date in it
EOMONTH(F2,-1)+1 is the first day of F2's month and EOMONTH(F2,0) is the last day. Put any date in F2 and the formula sums its whole month, with no need to remember whether a month has 30 or 31 days.
For a month-by-month table, list the first day of each month in a column and point F2 at that cell. Fill down and every row sums its own month.
Result: 6205
6,205 for October again, found from the single date in F2.
Year to date and the last 30 days
For year to date, start at January 1 of the end date's year with DATE(YEAR(G2),1,1). For a rolling window, subtract from the end date: G2-29 to G2 is 30 days including both ends.
In a live report, use TODAY() as the end date: =SUMIFS(D2:D9,A2:A9,">="&TODAY()-29,A2:A9,"<="&TODAY()). The total then moves forward each day on its own.
Result: 7455
7,455: everything from January 1 to October 31, 2026, including the September sale.
When the dates include times
Timestamps from a till or web shop include the time of day. October 31 at 3:15 PM is larger than the plain date October 31, which Excel treats as midnight, so "<="&G2 drops the whole last day.
Use "less than the next day" for the end instead. It works whether or not the cells hold times, so make it your default.
Result: 6205
6,205 here because these dates have no times. With timestamps this version still counts sales made late on October 31.
Dates stored as text are skipped
Row 9 holds 10/17/2026 as text, which often happens with data pasted from another system. SUMIFS compares dates as numbers and silently skips text, so the October total is 1,100 short. =COUNT(A2:A9) returns 7, not 8, which confirms one date is not a real date.
Convert the column with Data > Text to Columns > Finish (choose the date order in step 3 if needed). Until then, a SUMPRODUCT that converts each value with -- includes the text date, as long as Excel can read it in your regional settings; in a day-first locale this one returns #VALUE!.
Result: 7305
7,305 in month-first (US) Excel: the 6,205 from real dates plus the 1,100 sale stored as text.
Common mistakes
Writing ">=F2" inside the quotes
Excel then compares with the text F2 and returns 0. Join the cell: ">="&F2.
Start and end dates swapped
No date can be after the end and before the start, so the result is 0. Check F2 is the earlier date.
Losing the last day
If the dates include times, use "<"&G2+1 for the end.
Typing dates as text like "10/1/2026" in the criteria
That string means January 10 on a day-first computer. Use DATE(2026,10,1) or a date cell.
FAQ
How do I sum values between two dates in Excel?
=SUMIFS(sum_range,date_range,">="&start,date_range,"<="&end), with start and end in cells.
Can I use SUMIF for a date range?
Not in one condition. Subtract two SUMIFs, =SUMIF(dates,">="&start,amounts)-SUMIF(dates,">"&end,amounts), or use SUMIFS.
How do I sum by month in Excel?
Put the first day of each month in a column and use =SUMIFS(amounts,dates,">="&E2,dates,"<="&EOMONTH(E2,0)), filled down. A PivotTable grouped by month also works.
Why does my SUMIFS with dates return 0?
The operator is not joined with &, the dates in the range are text, or the start date is later than the end date.
Functions used
SUMIFSAdds with several conditionsEOMONTHLast day of a monthDATEBuilds a dateYEARYear of a dateSUMPRODUCTMultiply then addCOUNTCounts numbers
Related guides
- Count cells between two dates in Excel
- SUMIFS with multiple criteria in Excel
- SUMIF vs SUMIFS: what is the difference?
- SUMIF returning 0 or not working: causes and fixes
- Get the quarter from a date in Excel
- Formulas for a monthly budget in Excel
Updated