Year-to-Date Totals in Excel

A year-to-date (YTD) sum in Excel adds every amount from 1 January of the current year up to today. SUMIFS does it in one formula by checking the date column twice: once for "on or after 1 January" and once for "on or before today". This guide is for anyone building a sales, expense or KPI report, and it also covers last year's comparison, fiscal years and a running YTD by month.

The sample data#

The examples use this sales log in A1:C9. It covers two years, which is normal for a real file and is exactly why a plain SUM won't do:

The as-of date, 31-May-2026, is in F1. The YTD total should include the four 2026 rows up to May and leave out June and everything from 2025.

The year to date sum formula#

The year-to-date formula is SUMIFS with the amount column first, then two conditions on the date column. In F2, type:

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

The result is 4,450 (1,200 + 800 + 1,500 + 950). The 18-Jun-2026 sale is after the as-of date, and the 2025 sales are before the start date, so none of them count.

For a report that always shows "up to today", put =TODAY() in F1 or replace F1 in the formula with TODAY(). The SUMIFS function guide explains each argument.

How the formula works#

Each part of the formula does one job:

The & joins the operator in quotes to the date. Excel stores dates as serial numbers (days since 1900), so the criteria becomes text like ">=46023". Writing ">=DATE(2026,1,1)" all inside the quotes would not work, because Excel would read it as plain text.

Use "<=" for the end date, not "<", so sales on the as-of date itself are included. If your dates also hold times, like 31-May-2026 14:30, use "<"&F1+1 instead to include the whole last day.

Use an as-of date cell instead of TODAY#

Keeping the end date in a cell makes the report easy to check and rerun. With TODAY(), the number changes every day, so you can't reproduce last Friday's figure. With an as-of cell, you type 31-May-2026 and get the YTD at the end of May, any time.

The same idea powers the month-to-date total: only the start date changes, from 1 January to the 1st of the month. For any other start and end, see summing between two dates.

Compare year to date with last year#

A YTD figure means more next to last year's YTD to the same day. Shift both dates back one year:

=SUMIFS(C2:C9,A2:A9,">="&DATE(YEAR(F1)-1,1,1),A2:A9,"<="&EDATE(F1,-12))

The result is 3,900 (1,800 + 2,100), all 2025 sales up to 31-May-2025. EDATE(F1,-12) returns the same day 12 months earlier, and it handles leap years: from 29-Feb-2028 it goes back to 28-Feb-2027.

If this formula is in F3, growth is:

=F2/F3-1

The result is about 0.141, or 14.1% when formatted as a percentage. Don't compare this year's YTD with all of last year: five months against twelve always looks like a fall. For whole-year totals, see how to sum by year.

Year to date for one region or product#

Add another range and criteria pair to SUMIFS for each extra condition. Year-to-date sales for the North region:

=SUMIFS(C2:C9,A2:A9,">="&DATE(YEAR(F1),1,1),A2:A9,"<="&F1,B2:B9,"North")

The result is 2,700 (1,200 + 1,500). Point the region at a cell, such as B2:B9,H2, and fill the formula down a list of regions to get YTD by region. The COUNTIFS and SUMIFS lesson practises these multi-condition totals with locked ranges.

Fiscal year to date#

Many companies run a fiscal (financial) year that doesn't start in January, such as 1 April to 31 March. The only change is the start date. For a year starting on 1 April:

=DATE(YEAR(F1)-(MONTH(F1)<4),4,1)

MONTH(F1)<4 is TRUE (1) in January to March, so the start moves back to April of the previous year. For 31-May-2026 it is FALSE (0), and the start is 1-Apr-2026. Use this date in place of DATE(YEAR(F1),1,1) in the YTD formula. Here the fiscal YTD is 950, the only sale between 1 April and 31 May 2026. For a different start month, change both 4s.

A running year-to-date total by month#

A running total shows how the year has built up, month by month. First total each month, for example with SUMIFS between the 1st and the end of each month; the guide on summing by month shows how. Then add a running column:

In C2, type =SUM($B$2:B2) and fill it down. The $ locks the start at B2 while the end moves down one row each time. The last row matches the SUMIFS answer, 4,450, which is a good check.

Practise it#

Try the exercise on this page: it builds the YTD total from an as-of date. Then practise dates in criteria in a live spreadsheet, where you total rows between two dates and build a monthly summary, and it checks each formula instantly. The COUNTIFS and SUMIFS lesson adds the extra conditions, like region or product.