Math
Sum every 3 cells into quarterly totals (OFFSET & step multiplier)
Roll up a 12-row monthly ledger into 4 consecutive 3-month quarterly totals automatically using SUM(OFFSET($B$2, (Quarter - 1) * 3, 0, 3, 1)).
Stop hand-editing =SUM(B2:B4), =SUM(B5:B7), =SUM(B8:B10) row by row!
When you drag =SUM(B2:B4) down one row in a summary block, relative references shift by +1 row to =SUM(B3:B5) (Feb–Apr) instead of jumping +3 rows to =SUM(B5:B7) (Apr–Jun Q2)!
Multiplying zero-based block index (D2 - 1) by block height 3 inside OFFSET($B$2, (D2 - 1) * 3, 0, 3, 1) steps the starting row cleanly to +0, +3, +6, +9 while holding height = 3 rows fixed—rolling 12 monthly rows into Q1–Q4 totals with a single draggable formula!
OFFSET is volatile in Excel, so many repeated OFFSET formulas can add recalculation work. The example is a small fixed ledger, not a large-workbook performance recommendation.
| Quarter Index (D) | Row Offset (D - 1) * 3 | 3-Cell Slice Evaluated | Quarterly Sum |
|---|---|---|---|
| 1 (Q1) | 0 rows below B2 | B2:B4 (10 + 20 + 30) | 60 |
| 2 (Q2) | +3 rows below B2 | B5:B7 (40 + 50 + 60) | 150 |
| 3 (Q3) | +6 rows below B2 | B8:B10 (70 + 80 + 90) | 240 |
| 4 (Q4) | +9 rows below B2 | B11:B13 (100 + 110 + 120) | 330 |
Sum every N = 3 vertical cells for Quarter index D2
D2 must be an integer from 1 through 4 and all 12 monthly amounts must be numeric. Signed adjustments and zero are allowed. This assumes consecutive January–December rows; it does not inspect dates or detect missing months. Changing block size also requires changing the data-size and index guards.
=IF(OR(COUNT(D2)<>1,COUNT($B$2:$B$13)<>12),"Invalid inputs",IF(OR(D2<1,D2>4,D2<>INT(D2)),"Invalid inputs",SUM(OFFSET($B$2, (D2 - 1) * 3, 0, 3, 1))))Average every 3 cells per quarter
Wrapping the identical OFFSET slice in AVERAGE computes quarterly monthly-run-rate averages.
=IF(OR(COUNT(D2)<>1,COUNT($B$2:$B$13)<>12),"Invalid inputs",IF(OR(D2<1,D2>4,D2<>INT(D2)),"Invalid inputs",AVERAGE(OFFSET($B$2, (D2 - 1) * 3, 0, 3, 1))))Try the example
Use the original values or change them to test your understanding.