# How to sum the last N values in a column in Excel

In Excel 365, keep the filled cells and take the last N: `=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))`.

**Excel:** `=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=SUM(CHOOSEROWS(FILTER(B2:B13,B2:B13<>""),SEQUENCE(E1,1,-E1)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Month | Signups |  | Last N | 3 |
| Jan | 412 |  | Total |  |
| Feb | 389 |  |  |  |
| Mar | 455 |  |  |  |
| Apr | 478 |  |  |  |
| May | 502 |  |  |  |
| Jun | 466 |  |  |  |
| Jul | 531 |  |  |  |
| Aug | 549 |  |  |  |
| Sep |  |  |  |  |
| Oct |  |  |  |  |
| Nov |  |  |  |  |
| Dec |  |  |  |  |

Formula in E2: `=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))`

Result: 1546

## How it works

FILTER drops the empty months, so the list ends at the latest entry. TAKE with a negative count keeps rows from the end: `-3` keeps June, July and August. When September is filled in, the window moves on by itself.

Google Sheets has no TAKE; CHOOSEROWS with negative positions (-3, -2, -1 from SEQUENCE) picks the same rows.

## Other ways

- `=SUMPRODUCT((ROW(B2:B13)-ROW(B2)>=COUNT(B2:B13)-E1)*B2:B13)`: Works in every version, as long as the numbers have no gaps above the last entry.

## Common mistakes

- TAKE on the whole column without FILTER takes the empty months at the bottom and returns 0.
- If N is larger than the number of entries, TAKE returns all of them; CHOOSEROWS returns #VALUE!. Wrap with MIN(E1,COUNT(B2:B13)) to be safe.

## Questions

**How do I sum the first N values?**

Use a positive count: `=SUM(TAKE(B2:B13,E1))`.

**How do I average the last N values?**

Swap SUM for AVERAGE: `=AVERAGE(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))`.

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-the-last-n-values