Date & Time
Sum by fiscal year (any fiscal start month: July, October, or April)
Calculate Fiscal Year start and end dates dynamically from FY number and StartMonth (e.g., FY2026 = Jul 1, 2025 to Jun 30, 2026) inside SUMIFS.
Derive exact [FY_Start, FY_End] bounds for any fiscal calendar convention
This example uses an ending-year label: a July-start FY2026 begins in July 2025.
Compute the start and an exclusive end at the next fiscal year’s start, then use >= start and < end.
| TxDate (A) | Revenue (B) | July-Start FY Label | In FY2026 (Jul 25–Jun 26)? |
|---|---|---|---|
| 2025-06-15 | $400 | FY2025 | No (Prior FY) |
| 2025-07-10 | $950 | FY2026 | Yes |
| 2026-02-20 | $1,250 | FY2026 | Yes (FY2026 Sum = $2,800) |
| 2026-06-30 | $600 | FY2026 | Yes |
| 2026-07-01 | $500 | FY2027 | No (Next FY) |
Dynamic Fiscal Year SUMIFS formula (any StartMonth 1–12)
Pass target FY (2026) in D2 and Fiscal Start Month (7 for July, 10 for October, 1 for January) in E2.
=LET(s, DATE(D2 - IF(E2 > 1, 1, 0), E2, 1), e, EOMONTH(s, 11)+1, SUMIFS(B2:B6, A2:A6, ">=" & s, A2:A6, "<" & e))Count transactions inside the target Fiscal Year
Uses identical dynamic s and e date bounds inside COUNTIFS.
=LET(s, DATE(D2 - IF(E2 > 1, 1, 0), E2, 1), e, EOMONTH(s, 11)+1, COUNTIFS(A2:A6, ">=" & s, A2:A6, "<" & e))Inputs and interpretation
This example names a fiscal year by its ending calendar year; other organizations use different labels. Use an integer start month 1 through 12 and a supported integer year. The exclusive upper bound includes timestamps throughout the last day. This is a 12-calendar-month year, not a 52/53-week fiscal calendar.
Try the example
Use the original values or change them to test your understanding.