YTD formula in Excel: year-to-date totals with SUMIFS

The YTD formula with SUMIFS

=SUMIFS(sum_range, date_range, ">="&start, date_range, "<="&end)

• start is 1 January of the report year: DATE(YEAR(C24),1,1). Taking the year from the report date means the formula rolls into the new year on its own.
• end is the report date itself, here the month-end key in C24.
• The comparison operator is text and the date is a number, so they are joined with &: ">="&DATE(…).

Both conditions point at the same date column. SUMIFS only adds a row when every condition is true, so the two together form a window. Add more pairs to split the YTD by store, product or region, exactly as in the SUMIFS examples.

Monthly data or daily transactions

The example stores one row per month with a month-end date (31 Jan, 28 Feb …). Then "<="&C24 with C24 = 31 Aug 2026 includes August, and the window covers eight months. Month-end keys come from =EOMONTH(date,0), which turns any order date into the last day of its month.

With daily transactions the same formula works with the report date as the end. If the dates carry a time of day, use "less than the next day" instead: "<"&C24+1. A dashboard that always shows today's YTD uses TODAY() in both places: =SUMIFS(B:B,A:A,">="&DATE(YEAR(TODAY()),1,1),A:A,"<="&TODAY()).

Worked example: YTD, prior-year YTD and YoY growth from monthly sales

Monthly sales of a fictional coffee chain from January 2025 to the still-open September 2026, stored with month-end dates (yellow inputs). Column C is a running YTD that restarts every January. Rows 24 to 31 calculate the YTD for the report month in C24, the prior-year YTD, the YoY growth and two classic errors.

Worked example: YTD, prior-year YTD and YoY growth from monthly sales

	A	B	C
1	Month end	Sales (USD)	Running YTD
2	31 Jan 2025	84,200	84,200
3	28 Feb 2025	79,800	164,000
4	31 Mar 2025	89,900	253,900
5	30 Apr 2025	91,500	345,400
6	31 May 2025	90,700	436,100
7	30 Jun 2025	87,900	524,000
8	31 Jul 2025	92,800	616,800
9	31 Aug 2025	93,600	710,400
10	30 Sep 2025	91,800	802,200
11	31 Oct 2025	90,400	892,600
12	30 Nov 2025	96,900	989,500
13	31 Dec 2025	103,200	1,092,700
14	31 Jan 2026	87,600	87,600
15	28 Feb 2026	85,400	173,000
16	31 Mar 2026	94,800	267,800
17	30 Apr 2026	86,900	354,700
18	31 May 2026	95,300	450,000
19	30 Jun 2026	94,600	544,600
20	31 Jul 2026	100,900	645,500
21	31 Aug 2026	101,200	746,700
22	30 Sep 2026	52,400	799,100
23
24	Report month (last closed)		31 Aug 2026
25	YTD 2026: 1 Jan to the report month		746,700
26	Prior-year YTD: 1 Jan 2025 to 31 Aug 2025		710,400
27	YoY growth of YTD		5.1%
28	Wrong: no upper date limit (includes open September)		799,100
29	Wrong: YTD against the full prior year		-31.7%
30	Months in the YTD window (COUNTIFS)		8
31	Average month YTD		93,337.50

Fictional, illustrative figures. September 2026 holds month-to-date sales only, so the report month is the last closed month, August 2026.

Report on the last closed month

The last data row, September 2026, is still open: it holds only the first days of the month. Drop the upper date limit and the YTD becomes 799,100 instead of 746,700, eight and a half months against eight months last year. Monthly reports therefore use the last closed month as the report month and keep the open month in a separate month-to-date (MTD) block.

Prior-year YTD and YoY growth

A YTD figure means little on its own. Compare it with the same window one year earlier: start on 1 January of the prior year and stop twelve months before the report month.

=SUMIFS(B2:B22,A2:A22,">="&DATE(YEAR(C24)-1,1,1),A2:A22,"<="&EOMONTH(C24,-12)) returns 710,400. YoY growth is then =C25/C26-1 = 746,700 ÷ 710,400 − 1 = 5.1%.

For month-end keys, EOMONTH(C24,-12) is safer than EDATE(C24,-12), because EOMONTH always lands on a month end, including 29 February. Comparing YTD with the full prior year is the classic error: eight months against twelve gives −31.7%, a collapse that never happened.

A running YTD column that restarts every January

Column C shows the YTD at every month: =SUMIFS($B$2:B2,$A$2:A2,">="&DATE(YEAR(A2),1,1)), filled down. The first row of each range is locked with $ and the end grows with every row, so each cell adds everything from the top to its own row, but only from 1 January of its own year. December 2025 shows the full year, 1,092,700, and January 2026 starts again at 87,600. The running YTD is what a cumulative chart of this year against last year plots.

Fiscal years, QTD and rolling 12 months

Only the start date changes for other windows:

• Fiscal year starting in April: DATE(YEAR(EDATE(C24,-3)),4,1). Moving the report month back three months finds the calendar year in which the fiscal year began, so August 2026 starts on 1 Apr 2026 and February 2027 still starts on 1 Apr 2026.
• Quarter-to-date: EOMONTH(C24,-MOD(MONTH(C24)-1,3)-1)+1 gives 1 July for August.
• Rolling 12 months: ">"&EOMONTH(C24,-12) as the start and "<="&C24 as the end, September 2025 to August 2026.

The average month of the YTD window is the YTD divided by the number of months, which COUNTIFS with the same two conditions returns: 746,700 ÷ 8 = 93,337.50.

Common mistakes

• Only one date condition. Without the upper limit the YTD runs to the last row and picks up the open month or future budget rows: 799,100 instead of 746,700.
• Comparing YTD with the full prior year. Eight months against twelve always looks like a decline. Use the prior-year YTD for the same months.
• The date inside the quotes. ">=DATE(2026,1,1)" is compared as text and matches nothing. Keep only the operator in quotes and join the date with &.
• Dates stored as text. Imported dates that are really text are skipped by SUMIFS without an error. Check with =ISNUMBER(A2) and convert them first.
• Mixing month-start and month-end keys. If the data use month-end dates but the report cell says 1 Aug 2026, "<="&1 Aug leaves out the August row (31 Aug). Use the same convention in the data and in the report cell.
• EDATE on month ends in a leap year. EDATE(28 Feb 2029,-12) returns 28 Feb 2028 and loses 29 February. EOMONTH(…,-12) always returns the month end.