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

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

**Excel:** `=SUMIFS(B2:B11,A2:A11,">="&DATE(YEAR(E1)-1,1,1),A2:A11,"<="&EDATE(E1,-12))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Amount |  | As of | 2026-05-15 |
| 2025-01-22 | 3120 |  | Same period last year |  |
| 2025-03-14 | 2875 |  |  |  |
| 2025-05-10 | 3340 |  |  |  |
| 2025-05-20 | 2990 |  |  |  |
| 2025-08-05 | 3610 |  |  |  |
| 2026-01-16 | 3405 |  |  |  |
| 2026-02-27 | 3080 |  |  |  |
| 2026-04-09 | 3725 |  |  |  |
| 2026-05-14 | 3190 |  |  |  |
| 2026-05-29 | 2860 |  |  |  |

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

Result: $9,335

## 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.

## Other ways

- `=SUMPRODUCT((A2:A11>=DATE(YEAR(E1)-1,1,1))*(A2:A11<=EDATE(E1,-12))*B2:B11)`: The 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/last-year-to-date-total