Lookup & Reference
OFFSET function: sum a dynamic trailing N-month window
Build a resizable range reference from an anchor cell with OFFSET(ref, rows, cols, height, width), and avoid out-of-bounds #REF! errors.
How OFFSET shifts and resizes a starting anchor
OFFSET(reference, rows, cols, [height], [width]) starts at an anchor cell, moves down by rows and right by cols, and returns a range of height rows by width columns.
To sum the most recent W months ending at month M in column B (where B1 is the header and months 1..6 sit in B2:B7), start at B1, move down M - W + 1 rows, stay in column offset 0, and request height W.
| Cell | Month (A) | Shipments (B) | Rolling Formula Check |
|---|---|---|---|
| Row 2 | M1 | 110 | Anchor header B1 + row offset 1 |
| Row 3 | M2 | 125 | |
| Row 4 | M3 | 140 | |
| Row 5 | M4 | 160 | Trailing 3 as of M4 (B3:B5) = 425 |
| Row 6 | M5 | 175 | |
| Row 7 | M6 | 190 | Trailing 3 as of M6 (B5:B7) = 525 |
Trailing window total formula
When D2 = 6 (latest month M6) and E2 = 3 (3-month window), D2 - E2 + 1 equals 4. Moving 4 rows down from B1 lands on B5, and height 3 spans B5:B7 (160 + 175 + 190 = 525).
=SUM(OFFSET(B1, D2 - E2 + 1, 0, E2, 1))Out-of-bounds and zero-size #REF! boundaries
If a negative row/column offset steps above row 1 or left of column A—or if height or width is less than 1—OFFSET returns #REF!.
=OFFSET(B2, -5, 0)Try the example
Use the original values or change them to test your understanding.