# How to sum by quarter in Excel

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)`.

**Excel:** `=SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Plan | Revenue |  | Year | 2026 |
| 2026-01-14 | Basic | 4200 |  | Quarter | Revenue |
| 2026-02-27 | Pro | 7850 |  | 1 |  |
| 2026-03-31 | Basic | 3900 |  | 2 |  |
| 2026-04-01 | Pro | 8125 |  | 3 |  |
| 2026-05-19 | Basic | 4475 |  | 4 |  |
| 2026-06-30 | Pro | 9300 |  |  |  |
| 2026-07-08 | Basic | 4610 |  |  |  |
| 2026-08-21 | Pro | 8740 |  |  |  |
| 2026-10-02 | Basic | 5025 |  |  |  |
| 2026-11-16 | Pro | 9980 |  |  |  |
| 2025-12-30 | Pro | 7200 |  |  |  |

Formula in F3, filled to F6: `=SUMPRODUCT((YEAR($A$2:$A$12)=$F$1)*(ROUNDUP(MONTH($A$2:$A$12)/3,0)=E3)*$C$2:$C$12)`

Result: $15,950; $21,900; $13,350; $15,005

## 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.

## Other ways

- `=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))`: 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)`.

## 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.

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-quarter