Spreadsheet Practice

How to calculate last year's year-to-date total in Excel

Formula (Excel and Google Sheets)

=SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12))

Move the YTD window back a year: =SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12)).

Result on the example
$9,335 in E2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

How it works

DATE(YEAR(E1)-1,1,1) is January 1 of last year, and EDATE(E1,-12) is the same day 12 months before the as-of date: May 15, 2025. SUMIFS adds the 2025 amounts in that window, which is the fair comparison for this year's YTD.

=SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12))

Result9335

  1. B2:B1110 rows × 1 col: $3,120, $2,875, $3,340, $2,990, …

    A range: 10 cells in one column.

  2. A2:A1110 rows × 1 col: 1/22/2025, 3/14/2025, 5/10/2025, 5/20/2025, …

    A range: 10 cells in one column.

  3. E15/15/2026

    The value in E1.

  4. YEAR(E1)2026

    YEAR: Year of a date.

    • serial_numberE15/15/2026
  5. YEAR(E1)-12025

    The - operator subtracts.

  6. DATE(YEAR(E1)-1,1,1)1/1/2025 (45658)

    DATE: Builds a date from parts.

    • yearYEAR(E1)-12025
    • month1
    • day1
  7. ">="&DATE(YEAR(E1)-1,1,1)">=45658"

    The & operator joins as text.

  8. -12-12

    Makes the value negative.

  9. EDATE(E1,-12)5/15/2025 (45792)

    EDATE: Date a number of months before or after.

    • start_dateE15/15/2026
    • months-12-12
  10. "<="&EDATE(E1,-12)"<=45792"

    The & operator joins as text.

  11. SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12))9335

    SUMIFS: Adds values that meet every condition.

    • sum_rangeB2:B1110 rows × 1 col: $3,120, $2,875, $3,340, $2,990, …
    • criteria_range1A2:A1110 rows × 1 col: 1/22/2025, 3/14/2025, 5/10/2025, 5/20/2025, …
    • criteria1">="&DATE(YEAR(E1)-1,1,1)">=45658"
    • criteria_range2A2:A1110 rows × 1 col: 1/22/2025, 3/14/2025, 5/10/2025, 5/20/2025, …
    • criteria2"<="&EDATE(E1,-12)"<=45792"

In Google Sheets

The same formula works in Google Sheets: =SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12)). We ran it in Google Sheets mode on the same data and it returns $9,335.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT((A2:A11>=DATE(YEAR(E1)-1,1,1))*(A2:A11<=EDATE(E1,-12))*B2:B11)$9,335The same window as two tests.

Common mistakes

  • E1-365 drifts by a day across leap years. EDATE moves by calendar months, so February 29 becomes February 28.
  • Comparing this year's YTD with last year's full-year total makes every year look like a decline until December.

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 last year's year-to-date total

In E2, total last year's amounts from January 1 up to the same day as the date in E1.

Questions

How do I calculate the growth versus last year?

Divide this year's YTD by last year's and subtract 1: =ytd/last_ytd-1, formatted as a percentage.

How do I do this for months instead of dates?

Use EOMONTH: from EOMONTH(E1,-13)+1 to EOMONTH(E1,-12) gives the same month last year.

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.