Month-to-Date Totals in Excel

A month-to-date total, often shortened to MTD, adds every amount dated from the 1st of the current month up to today. In Excel, you get a month to date sum with SUMIFS and two date conditions, using EOMONTH to find the first day of the month. This guide builds the formula step by step, then shows how to compare it with last month and project the full month.

The month to date sum formula#

Use SUMIFS with a start date and an end date. With dates in column A and amounts in column C:

=SUMIFS(C:C,A:A,">="&EOMONTH(TODAY(),-1)+1,A:A,"<="&TODAY())

It adds every amount whose date is on or after the 1st of this month and on or before today. Because it uses TODAY(), the total moves forward by itself each day, and it starts again from zero on the 1st of each month.

All the examples below use this sales log, with F1 holding an as-of date of 18 March 2026 instead of TODAY(), so the results stay the same:

=SUMIFS(C2:C8,A2:A8,">="&EOMONTH(F1,-1)+1,A2:A8,"<="&F1)

The result is 900: the four March sales up to the 18th (250 + 300 + 150 + 200). The 24 March sale is after the as-of date, and the February sales are before the start date.

How the formula works#

Each part of the formula has one job:

EOMONTH stands for "end of month". EOMONTH(date, months) returns the last day of the month that is months away from the date. With -1 you get the end of last month, and one day later is the start of this month. The EOMONTH function guide shows more uses.

The & joins the comparison operator to the date. Writing ">=EOMONTH(F1,-1)+1" inside the quotes wouldn't work, because Excel would read it as plain text. If you prefer, DATE(YEAR(F1),MONTH(F1),1) gives the same first day.

SUMIFS works in every current version of Excel. The SUMIFS function guide explains how the range and criteria pairs fit together.

Use an as-of date cell instead of TODAY#

Point the formula at a date cell when you need to check the total for a past day, or when a report must show a fixed date. Type the date in F1, or put =TODAY() in F1 so one cell controls the whole sheet.

This has two benefits. Every MTD formula on the sheet reads the same date, so they always agree. And you can type any date into F1 to answer questions like "what were sales month to date on the 10th?" With 10 March 2026 in F1, the same formula returns 550 (250 + 300).

Compare month to date with last month#

To compare fairly, sum last month over the same number of days. EDATE moves a date by whole months, so EDATE(F1,-1) is 18 February 2026:

=SUMIFS(C2:C8,A2:A8,">="&EOMONTH(F1,-2)+1,A2:A8,"<="&EDATE(F1,-1))

EOMONTH(F1,-2)+1 is 1 February. The result is 400: only the 10 February sale falls between 1 and 18 February. Comparing 900 with 400 shows March is ahead:

=(900-400)/400

That is a change of 125%. In a real sheet, use the two cells that hold the totals instead of typed numbers. Comparing March MTD with the whole of February (1,000) would be unfair, because February had more days to collect sales.

When the as-of date is the 31st, EDATE returns the last day of a shorter month, such as 28 February. That is usually what you want.

Month-to-date for one rep, product or region#

Add more pairs to SUMIFS to narrow the total. Ben's month-to-date sales are:

=SUMIFS(C2:C8,A2:A8,">="&EOMONTH(F1,-1)+1,A2:A8,"<="&F1,B2:B8,"Ben")

The result is 450 (250 + 200). To count deals instead of adding amounts, swap SUMIFS for COUNTIFS and drop the sum range: the same date pairs give 4 sales this month to date. The guide to counting by month covers COUNTIFS with dates.

Project the full month from month to date#

A simple projection divides the MTD total by the days so far and multiplies by the days in the month:

=900/DAY(F1)*DAY(EOMONTH(F1,0))

DAY(F1) is 18, and DAY(EOMONTH(F1,0)) is 31, the number of days in March. The projection is 900 ÷ 18 × 31 = 1,550. It assumes sales keep the same daily pace, so treat it as a rough guide, not a forecast.

The same idea extends to longer periods. A year-to-date total uses the 1st of January as the start date, and summing by month gives one total per full month. For weekly views, see how to sum by week.

Why is the month-to-date total wrong?#

When the MTD total looks too low or shows 0, check these in order:

1. Dates include times. A sale at 14:30 today is later than today's date, which means midnight. "<="&TODAY() skips it. Use "<"&TODAY()+1 as the end condition.
2. Dates are text. Imported dates that sit on the left of the cell are often text, and SUMIFS can't compare them. Convert them to real dates first.
3. The criteria are inside the quotes. ">=F1" compares with the letters F1. Write ">="&F1.
4. Ranges are different sizes. In SUMIFS, the sum range and each criteria range must cover the same rows, or you get #VALUE!.
5. You expected future-dated rows. Orders dated later this month are left out by design, because they are not "to date".

For other date windows, such as the last 30 days or a custom period, see how to sum between two dates.

Practise it#

Date conditions are easier once you've typed a few. Practise dates in criteria in a live spreadsheet: you'll build totals by date range and by month, and each formula is checked instantly. Then use the COUNTIFS and SUMIFS lesson to add rep and region conditions on top.