Sum the Last N Rows with OFFSET

Excel Formulas › Lookup

All versionsOFFSET

OFFSET returns a range that’s shifted and resized from a starting point — perfect for a moving window like “the last 3 months” that follows your data as it grows. Combine it with COUNT to always grab the most recent N values.

Quick formula: to sum the last N values in a growing column B (N in E1):
=SUM(OFFSET(B1, COUNT(B:B), 0, -E1, 1))
OFFSET jumps to the bottom of the data, then grabs a block E1 rows tall reaching back up — the last N values.

Functions used (tap for the full reference guide):

OFFSETCOUNTSUM

The example

Summing the last 3 months, which follows the data downward.

AB
1MonthSales
2Jan$100
3Feb$140
4Mar$120
5Apr$180
6May$150
7Last 3 total:$450

The formula

Sum of the last 3 months:

=SUM(OFFSET(B1, COUNT(B2:B100), 0, -3, 1)) // Mar+Apr+May = 450

How it works

OFFSET defines the window; COUNT finds the bottom:

  1. COUNT(B2:B100) counts the data rows — so OFFSET knows where the bottom of the list is.
  2. OFFSET(B1, thatCount, 0, -3, 1) starts at B1, moves down to the last value, then takes a block 3 rows tall (the -3 reaches upward) and 1 column wide.
  3. That block is the last 3 values; SUM totals them.
  4. Add a new month and the window slides automatically — it always covers the most recent 3.

OFFSET is the classic dynamic-range engine — for moving averages, expanding charts, and self-resizing named ranges. In Excel 365, INDEX or the spill # operator often replace it with non-volatile alternatives.

Try it: interactive demo

Live demo

Sum the last N of {100,140,120,180,150}.

Total:

Variations

Average the last N (moving average)

Swap SUM for AVERAGE:

=AVERAGE(OFFSET(B1, COUNT(B2:B100), 0, -3, 1))

Non-volatile with INDEX

A faster alternative for the last N:

=SUM(INDEX(B:B, COUNT(B:B)-2):INDEX(B:B, COUNT(B:B)+1))

A self-resizing named range

Name a range as =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1) for charts.

Pitfalls & errors

OFFSET is volatile. It recalculates on every change. For large models, the INDEX-based alternatives are faster.

#REF! if the window runs off the sheet. Asking for more rows than exist pushes OFFSET out of bounds. Cap N at the data count.

COUNT ignores text. If your column mixes text and numbers, use COUNTA for the row count so the bottom is found correctly.

Practice workbook

📊
Download the free Sum the Last N Rows with OFFSET practice workbook
A growing list with the live OFFSET last-N sum, the moving-average and INDEX-based variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum the last N rows in Excel?
Use OFFSET to grab a moving window: =SUM(OFFSET(B1, COUNT(B2:B100), 0, -N, 1)). COUNT finds the bottom of the data and OFFSET takes the last N rows upward.
How do I make a moving average of recent values?
Replace SUM with AVERAGE: =AVERAGE(OFFSET(B1, COUNT(B2:B100), 0, -3, 1)) averages the last three values and follows the data as it grows.
Is there a non-volatile alternative to OFFSET?
Yes, INDEX builds a range without being volatile, e.g. =SUM(INDEX(B:B, n-2):INDEX(B:B, n+1)), which is faster in large workbooks.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Moving average · Dynamic named range · Sum the last/first non-blank

Function references: OFFSET · COUNT · SUM