How to sum by fiscal year in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)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.
- Result on the example
- one result per row in
F2, filled down toF3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F3. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)Result22010
$A$2:$A$98 rows × 1 col: 6/20/2025, 7/1/2025, 10/14/2025, 1/9/2026, …A range: 8 cells in one column.
EDATE($A$2:$A$9,6){8 rows × 1 col} 46011, 46023, 46126, 46212, …EDATE: Date a number of months before or after.- start_date
$A$2:$A$98 rows × 1 col: 6/20/2025, 7/1/2025, 10/14/2025, 1/9/2026, … - months
6
- start_date
YEAR(EDATE($A$2:$A$9,6)){8 rows × 1 col} 2025, 2026, 2026, 2026, …YEAR: Year of a date.- serial_number
EDATE($A$2:$A$9,6){8 rows × 1 col} 46011, 46023, 46126, 46212, …
- serial_number
E22026The value in E2.
YEAR(EDATE($A$2:$A$9,6))=E2{8 rows × 1 col} FALSE, TRUE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
$C$2:$C$98 rows × 1 col: $4,200, $3,850, $6,125, $2,980, …A range: 8 cells in one column.
YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9{8 rows × 1 col} 0, 3850, 6125, 2980, …The * operator multiplies.
SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)22010SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9{8 rows × 1 col} 0, 3850, 6125, 2980, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS($C$2:$C$9,$A$2:$A$9,">="&DATE(E2-1,7,1),$A$2:$A$9,"<="&DATE(E2,6,30)) | $22,010, $8,150 | 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.
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.
Sum by fiscal year
F2, total the spend for the fiscal year in E2 (July 1 to June 30, named by the year it ends), then fill down to F3.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)).
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.