Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum values by fiscal quarter in Excel and Google Sheets
Topic:Date arithmeticExcel & Google Sheets
=SUMPRODUCT((INT(MOD(MONTH(A2:A9)-C2+12,12)/3)+1=E2)*(YEAR(A2:A9)+(MONTH(A2:A9)<C2)=F2)*B2:B9)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put transaction dates in A2:A9, amounts in B2:B9, and the fiscal year start month in C2 (e.g. 4 for April).
- 2The fiscal quarter formula is INT(MOD(MONTH(date)-start_month+12,12)/3)+1. The +12 inside MOD prevents negative remainders when a date month is earlier than the fiscal start month.
- 3The fiscal year formula is YEAR(date)+(MONTH(date)<start_month). Dates before the fiscal year start month belong to the prior fiscal year.
- 4In E2 enter the fiscal quarter number (1-4) you want to sum. In F2 enter the fiscal year. In G2 enter the full SUMPRODUCT formula.
- 5Verify: for a July-start fiscal year, July-September = Q1, October-December = Q2, January-March = Q3, April-June = Q4.
Tips
- The +12 inside MOD is the critical correction — without it, months before the fiscal start produce negative values and wrong quarter assignments.
- For a fiscal year starting July 1, set C2=7. Test by checking that a June date maps to Q4 and a July date maps to Q1.
- For large datasets, add a helper column with the fiscal quarter key as a text string (e.g. "FY2025-Q2") and use the faster SUMIF on that column instead.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I sum by fiscal quarter when the fiscal year starts April 1?
Set C2 to 4. January, February, and March dates will map to Q4 of the prior fiscal year automatically.
Can I use a pivot table for fiscal quarters?
Not directly — pivot tables use calendar quarters. Add a helper column with the fiscal quarter label and use that as a pivot field.
More on Date arithmetic
See all →Reference
TODAY / DATEDIF
Work with the current date and measure the gap between two dates.
How-toHow to calculate age from a date of birth
Turn a birth date into an age in years with DATEDIF and TODAY. Works the same in Excel and Google Sheets.
ReferenceDATEDIF
Calculates the difference between two dates in years, months, or days using a unit code string.
How-toHow to calculate days between two dates
Subtract one date from another to get the number of days, or count working days with NETWORKDAYS. Works in both apps.
How-toHow to add months to a date
Shift a date forward or back by a number of months with EDATE — it handles month lengths correctly. Works in both apps.
How-toHow to flag overdue dates
Mark rows whose due date has passed by comparing it to TODAY() with IF. Works the same in Excel and Google Sheets.