How to calculate a year-to-date (YTD) total in Excel

Q: How do I make it always use today?

Put =TODAY() in F1, or use TODAY() in place of F1 in the formula.

Q: How do I calculate month-to-date?

Start the window at the first of the month: =SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1).

Q: How do I compare with the same period last year?

Move both ends back a year: from DATE(YEAR(F1)-1,1,1) to EDATE(F1,-12).

Q: How do I calculate fiscal YTD for a July start?

Start at the most recent July 1: DATE(YEAR(F1)-(MONTH(F1)<7),7,1).

Formula (Excel and Google Sheets)

=SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1)

Use SUMIFS from January 1 of the as-of date's year to the as-of date: =SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1).

Result on the example: $15,580 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

DATE(YEAR(F1),1,1) is January 1 of the as-of year, and the as-of date itself is the end of the window. SUMIFS adds the amounts dated in between, so the 2025 rows and anything after May 15 stay out. Change F1 and the total follows.

=SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1)

Result15580

C2:C109 rows × 1 col: $3,150, $2,840, $2,975, $3,310, …
A range: 9 cells in one column.
A2:A109 rows × 1 col: 11/18/2025, 12/22/2025, 1/9/2026, 2/13/2026, …
A range: 9 cells in one column.
F15/15/2026
The value in F1.
YEAR(F1)2026
YEAR: Year of a date.
- serial_numberF15/15/2026
DATE(YEAR(F1),1,1)1/1/2026 (46023)
DATE: Builds a date from parts.
- yearYEAR(F1)2026
- month1
- day1
">="&DATE(YEAR(F1),1,1)">=46023"
The & operator joins as text.
"<="&F1"<=46157"
The & operator joins as text.
SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1)15580
SUMIFS: Adds values that meet every condition.
- sum_rangeC2:C109 rows × 1 col: $3,150, $2,840, $2,975, $3,310, …
- criteria_range1A2:A109 rows × 1 col: 11/18/2025, 12/22/2025, 1/9/2026, 2/13/2026, …
- criteria1">="&DATE(YEAR(F1),1,1)">=46023"
- criteria_range2A2:A109 rows × 1 col: 11/18/2025, 12/22/2025, 1/9/2026, 2/13/2026, …
- criteria2"<="&F1"<=46157"

In Google Sheets

The same formula works in Google Sheets: =SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1). We ran it in Google Sheets mode on the same data and it returns $15,580.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT((YEAR(A2:A10)=YEAR(F1))(A2:A10<=F1)C2:C10)	$15,580	The same window written as two tests.

Common mistakes

Using TODAY() inside the formula makes old reports change every day. Keep the as-of date in a cell so a report can be rerun for any date.
If the data is entered with times, use "<"&F1+1 as the end so the whole as-of day is included.
Typing January 1 into the formula, like ">=1/1/2026", breaks next year. Build it from the as-of date with 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 a year-to-date (YTD) total

In F2, total the amounts from January 1 of the year in F1 up to and including the date in F1.

Questions

How do I make it always use today?

Put =TODAY() in F1, or use TODAY() in place of F1 in the formula.

How do I calculate month-to-date?

Start the window at the first of the month: =SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1).

How do I compare with the same period last year?

Move both ends back a year: from DATE(YEAR(F1)-1,1,1) to EDATE(F1,-12).

How do I calculate fiscal YTD for a July start?

Start at the most recent July 1: DATE(YEAR(F1)-(MONTH(F1)<7),7,1).

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.