# How to sum by fiscal year in Excel

Shift each date forward by the months before the fiscal year starts, then compare years: `=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)` for a year starting in July.

**Excel:** `=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Department | Spend |  | Fiscal year | Spend |
| 2025-06-20 | Ops | 4200 |  | 2026 |  |
| 2025-07-01 | Ops | 3850 |  | 2027 |  |
| 2025-10-14 | IT | 6125 |  |  |  |
| 2026-01-09 | Ops | 2980 |  |  |  |
| 2026-04-22 | IT | 5340 |  |  |  |
| 2026-06-30 | Ops | 3715 |  |  |  |
| 2026-07-01 | IT | 4890 |  |  |  |
| 2026-09-15 | Ops | 3260 |  |  |  |

Formula in F2, filled to F3: `=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)`

Result: $22,010; $8,150

## How it works

Adding six months with EDATE moves July 2025 into January 2026, so `YEAR(EDATE(date,6))` is the fiscal year that ends the next June. SUMPRODUCT adds the spend whose fiscal year matches E2. June 30 and July 1 fall on the right sides of the line.

## Other ways

- `=SUMIFS($C$2:$C$9,$A$2:$A$9,">="&DATE(E2-1,7,1),$A$2:$A$9,"<="&DATE(E2,6,30))`: Builds the start and end date of each fiscal year. Faster on large data.

## Common mistakes

- Some organizations name the fiscal year by the year it starts. If FY2026 means July 2026 to June 2027, use `YEAR(EDATE(date,-6))`.
- EDATE needs real dates. Text dates return #VALUE! inside SUMPRODUCT.

## Questions

**What if the fiscal year starts in April or October?**

Shift by the months before the start: April needs EDATE(date,9), October needs EDATE(date,3), when the year is named by its end.

**How do I show the fiscal year next to each date?**

Use `="FY"&YEAR(EDATE(A2,6))`.

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/sum-by-fiscal-year