How to calculate month-to-date (MTD) sales in Excel
Formula (Excel and Google Sheets)
=SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1)Use SUMIFS from the first of the month to the as-of date: =SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1).
- Result on the example
- $10,240 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
EOMONTH(F1,-1)+1 is the first day of the as-of month. The two conditions keep sales from that day through the as-of date, so the September rows and the sales after October 21 are left out.
=SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1)Result10240
C2:C109 rows × 1 col: $2,140, $1,875, $2,310, $1,690, …A range: 9 cells in one column.
A2:A109 rows × 1 col: 9/28/2026, 9/30/2026, 10/1/2026, 10/6/2026, …A range: 9 cells in one column.
F110/21/2026The value in F1.
-1-1Makes the value negative.
EOMONTH(F1,-1)9/30/2026 (46295)EOMONTH: Last day of the month, months away.- start_date
F110/21/2026 - months
-1-1
- start_date
EOMONTH(F1,-1)+146296The + operator adds.
">="&EOMONTH(F1,-1)+1">=46296"The & operator joins as text.
"<="&F1"<=46316"The & operator joins as text.
SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1)10240SUMIFS: Adds values that meet every condition.- sum_range
C2:C109 rows × 1 col: $2,140, $1,875, $2,310, $1,690, … - criteria_range1
A2:A109 rows × 1 col: 9/28/2026, 9/30/2026, 10/1/2026, 10/6/2026, … - criteria1
">="&EOMONTH(F1,-1)+1">=46296" - criteria_range2
A2:A109 rows × 1 col: 9/28/2026, 9/30/2026, 10/1/2026, 10/6/2026, … - criteria2
"<="&F1"<=46316"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1). We ran it in Google Sheets mode on the same data and it returns $10,240.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((A2:A10>=EOMONTH(F1,-1)+1)*(A2:A10<=F1)*C2:C10) | $10,240 | The same total in any version. |
| =SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1,B2:B10,"Downtown") | $6,570Different result | Month-to-date for one store: add a condition pair. Downtown only. |
Common mistakes
- Without the end condition, future-dated rows such as scheduled orders count toward MTD.
- Typing the month into the formula breaks next month. Build both ends from the as-of date.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate month-to-date (MTD) sales
F2, total the sales from the first day of the month of F1 up to and including the date in F1.Questions
How do I compare with the same days last month?
=SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-2)+1,A2:A10,"<="&EDATE(F1,-1)).
How do I make it update every day?
Put =TODAY() in F1.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.