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

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)`.

**Excel:** `=SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Channel | Amount |  | As of | 2026-05-15 |
| 2025-11-18 | Retail | 3150 |  | YTD total |  |
| 2025-12-22 | Online | 2840 |  |  |  |
| 2026-01-09 | Retail | 2975 |  |  |  |
| 2026-02-13 | Online | 3310 |  |  |  |
| 2026-03-06 | Retail | 2690 |  |  |  |
| 2026-04-17 | Online | 3585 |  |  |  |
| 2026-05-15 | Retail | 3020 |  |  |  |
| 2026-05-28 | Online | 3460 |  |  |  |
| 2026-06-11 | Retail | 2815 |  |  |  |

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

Result: $15,580

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

## Other ways

- `=SUMPRODUCT((YEAR(A2:A10)=YEAR(F1))*(A2:A10<=F1)*C2:C10)`: 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.

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

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/year-to-date-total