Cellwise Try it free

Excel guide · 4 min read

Sum the last N values in a column

Quick answerFor a list with a header in row 1 and no gaps, =SUM(INDEX(B:B,COUNTA(B:B)-6):INDEX(B:B,COUNTA(B:B))) adds the last 7 values and moves down as you add rows. In Excel 365 or 2024, =SUM(TAKE(TOCOL(B2:B1000,1),-7)) does the same and skips blank cells.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1DateTakingsLast N
2Oct 20, 20266127
3Oct 21, 2026548
4Oct 22, 2026701
5Oct 23, 2026866
6Oct 24, 20261043
7Oct 25, 2026920
8Oct 27, 2026575
9Oct 28, 2026634
10Oct 29, 2026688
11Oct 30, 2026910

Download this sheet as an Excel practice file (.xlsx)

Step 1: find the last row

A café logs its takings every trading day, one row per day. The total for "the last 7 days of trading" has to move down each time a new row is added, so it cannot be a fixed range like B5:B11.

COUNTA(B:B) counts the filled cells in column B. With one header and 10 numbers it returns 11, which is also the row number of the last entry, as long as the data starts in row 2 and has no gaps. MATCH(9.99E+307,B:B) gives the same 11 a different way: it looks for a number bigger than any real one and returns the position of the last number in the column, even if there are gaps above it.

=MATCH(9.99E+307,B:B)

Result: 11

11: the last number in column B is in row 11 (October 30).

Step 2: build the range with INDEX

INDEX(B:B,n) normally returns a value, but next to a colon it returns a cell reference. So INDEX(B:B,COUNTA(B:B)-D2+1):INDEX(B:B,COUNTA(B:B)) becomes B5:B11 here: from 7 rows up to the last row. Wrap it in SUM, with the number of values in D2: =SUM(INDEX(B:B,COUNTA(B:B)-D2+1):INDEX(B:B,COUNTA(B:B))).

This works in every version of Excel. Unlike OFFSET, INDEX is not volatile, so it does not recalculate every time anything changes. Swap SUM for AVERAGE to get the average of the last 7 days. The check below sums the range that formula points to.

=SUM(B5:B11)

Result: 5636

5,636: the takings from October 23 to October 30, the last 7 entries.

One formula with SUMPRODUCT (Excel 365 and 2021)

If the range trick feels fragile, number the rows instead. SEQUENCE(ROWS(B2:B500)) gives each row a position from 1 to 499. COUNT(B2:B500) is how many entries there are, so positions above COUNT minus 7 are the last 7. Multiplying the TRUE/FALSE test by the values keeps only those.

The range can run far past the data, so new rows are picked up without editing the formula. Like the INDEX version, it assumes the entries have no gaps.

=SUMPRODUCT((SEQUENCE(ROWS(B2:B500))>COUNT(B2:B500)-D2)*B2:B500)

Result: 5636

5,636, the same last 7 entries. Change D2 to 3 and you get the last three.

Last 7 entries or last 7 days?

They are not the same thing. The café was closed on Monday October 26, so the last 7 entries go back to October 23. The last 7 calendar days, October 24 to 30, hold only six trading days. Decide which one your report means.

For calendar days, use SUMIFS on the date column. MAX(A:A) is the latest date, so ">"&MAX(A:A)-7 keeps dates after October 23. Use TODAY() instead of MAX(A:A) if the window should end today even when today has no entry yet.

=SUMIFS(B:B,A:A,">"&MAX(A:A)-D2)

Result: 4770

4,770: the last 7 calendar days, which contain only six days of takings.

A rolling 7-day total on every row

To see the trend, put a moving total next to each day. In C8 (the seventh entry) type =SUM(B2:B8) and fill it down. Both ends of the range are relative, so in C9 it becomes =SUM(B3:B9), then =SUM(B4:B10), always the current row and the six above it.

Leave C2 to C7 empty, since those rows do not have 7 days behind them yet. Use AVERAGE in place of SUM for a 7-day moving average, which smooths out busy weekends.

=SUM(B2:B8)

Result: 5265

5,265 in C8: October 20 to 27. Filled down, C11 shows 5,636.

Columns with gaps: TAKE and TOCOL in Excel 365

COUNTA and COUNT count only filled cells, so a blank day in the middle of the list makes them come up short, and the INDEX range ends one row too early. The last entry is silently left out.

In Excel 365 and Excel 2024, TOCOL(B2:B1000,1) lists the values with blanks removed (the 1 means "ignore blanks"), and TAKE(...,-7) keeps the last seven. So =SUM(TAKE(TOCOL(B2:B1000,1),-7)) sums the last seven entries whatever gaps there are. In older versions, use MATCH(9.99E+307,B:B) as the end row in the INDEX formula, and remember that a blank inside the window then means only six values are added.

Common mistakes

The total includes the header or starts one row early

COUNTA(B:B) equals the last row only when there is exactly one header row and no gaps. With two header rows, add 1 to it.

The last entry is left out

There is a blank cell inside the data. Fill it with 0 or use MATCH(9.99E+307,B:B) or TOCOL to find the end.

Mixing up last 7 entries and last 7 days

Missing days make the two differ. Use SUMIFS on the date column for calendar days.

Totals elsewhere in column B

A SUM at the bottom of column B is counted as an entry. Keep totals in another column.

FAQ

How do I sum the last 7 values in a column in Excel?

Use =SUM(INDEX(B:B,COUNTA(B:B)-6):INDEX(B:B,COUNTA(B:B))) when the data starts in row 2 under one header. In Excel 365 or 2024, =SUM(TAKE(TOCOL(B2:B1000,1),-7)) also works.

How do I sum the last N rows dynamically?

Put N in a cell such as D2 and use it in the formula: =SUM(INDEX(B:B,COUNTA(B:B)-D2+1):INDEX(B:B,COUNTA(B:B))). The range moves down as new rows are added.

How do I sum the last 7 days in Excel?

If you mean calendar days, use the dates: =SUMIFS(B:B,A:A,">"&TODAY()-7) for the seven days up to today, or ">"&MAX(A:A)-7 for the seven days up to the latest entry.

Can I use OFFSET to sum the last N values?

Yes, =SUM(OFFSET(B1,COUNT(B:B)-6,0,7,1)) works, but OFFSET is volatile and recalculates whenever anything changes. INDEX gives the same result without that cost.

Functions used

Related guides

Updated