How to sum by quarter in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12)Work out each date's quarter with ROUNDUP(MONTH(date)/3,0) and add the matching rows: =SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12).
- Result on the example
- one result per row in
F3, filled down toF6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F3, filled down to F6. Change any input and the result updates, or edit the formula to experiment.
How it works
Months 1 to 3 divided by 3 give 0.33 to 1, which ROUNDUP turns into 1; months 4 to 6 become 2, and so on. SUMPRODUCT keeps the rows whose quarter matches E3 and whose year matches F1, then adds their revenue. The December 2025 row is left out by the year test.
=SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12)Result15950
$A$2:$A$1211 rows × 1 col: 1/14/2026, 2/27/2026, 3/31/2026, 4/1/2026, …A range: 11 cells in one column.
YEAR($A$2:$A$12){11 rows × 1 col} 2026, 2026, 2026, 2026, …YEAR: Year of a date.- serial_number
$A$2:$A$1211 rows × 1 col: 1/14/2026, 2/27/2026, 3/31/2026, 4/1/2026, …
- serial_number
$F$12026The value in F1.
YEAR($A$2:$A$12)=$F$1{11 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
MONTH($A$2:$A$12){11 rows × 1 col} 1, 2, 3, 4, …MONTH: Month of a date, 1–12.- serial_number
$A$2:$A$1211 rows × 1 col: 1/14/2026, 2/27/2026, 3/31/2026, 4/1/2026, …
- serial_number
MONTH($A$2:$A$12)/3{11 rows × 1 col} 0.3333333333, 0.6666666667, 1, 1.333333333, …The / operator divides.
ROUNDUP(MONTH($A$2:$A$12)/3,0){11 rows × 1 col} 1, 1, 1, 2, …ROUNDUP: Rounds away from zero.- number
MONTH($A$2:$A$12)/3{11 rows × 1 col} 0.3333333333, 0.6666666667, 1, 1.333333333, … - num_digits
0
- number
E31The value in E3.
ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3{11 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3{11 rows × 1 col} 1, 1, 1, 0, …The * operator multiplies.
$C$2:$C$1211 rows × 1 col: $4,200, $7,850, $3,900, $8,125, …A range: 11 cells in one column.
YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12{11 rows × 1 col} 4200, 7850, 3900, 0, …The * operator multiplies.
SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12)15950SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12{11 rows × 1 col} 4200, 7850, 3900, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12). 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$12,$A$2:$A$12,">="&DATE($F$1,E3*3-2,1),$A$2:$A$12,"<="&EOMONTH(DATE($F$1,E3*3,1),0)) | $15,950, $21,900, $13,350… | Builds each quarter's first and last day. SUMIFS is faster on tens of thousands of rows. |
Common mistakes
- Without the year test, Q1 2025 and Q1 2026 are added together whenever the data spans more than one year.
- INT(MONTH/3) is a common slip: it gives 0 for January and February and 1 for March. Use ROUNDUP, or
INT((MONTH(A2)+2)/3).
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 quarter
F3, total the revenue for the quarter in E3 of the year in F1, then fill down to F6.Questions
How do I show the quarter next to each date?
Use ="Q"&ROUNDUP(MONTH(A2)/3,0), or =YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0) to include the year.
How do I sum by fiscal quarter?
Shift the date first. For a year starting in July: ROUNDUP(MONTH(EDATE(A2,6))/3,0) gives the fiscal quarter.
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.