Spreadsheet Practice

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

Excel formula

=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))

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

Result on the example
1546 in E2
Works in
Excel 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

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.

=SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))

Result1546

  1. B2:B1312 rows × 1 col: 412, 389, 455, 478, …

    A range: 12 cells in one column.

  2. B2:B13<>""{12 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …

    The <> operator tests whether they differ.

  3. FILTER(B2:B13,B2:B13<>""){8 rows × 1 col} 412, 389, 455, 478, …

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeB2:B1312 rows × 1 col: 412, 389, 455, 478, …
    • condition1B2:B13<>""{12 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
  4. E13

    The value in E1.

  5. -E1-3

    Makes the value negative.

  6. TAKE(FILTER(B2:B13,B2:B13<>""),-E1){3 rows × 1 col} 466, 531, 549

    TAKE: First (or last, if negative) rows or columns.

    • arrayFILTER(B2:B13,B2:B13<>""){8 rows × 1 col} 412, 389, 455, 478, …
    • rows-E1-3
  7. SUM(TAKE(FILTER(B2:B13,B2:B13<>""),-E1))1546

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1TAKE(FILTER(B2:B13,B2:B13<>""),-E1){3 rows × 1 col} 466, 531, 549

In Google Sheets

Google Sheets needs a different formula: =SUM(CHOOSEROWS(FILTER(B2:B13,B2:B13<>""),SEQUENCE(E1,1,-E1))). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT((ROW(B2:B13)-ROW(B2)>=COUNT(B2:B13)-E1)*B2:B13)1546Works 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Sum the last N values in a column

In E2, add up the last E1 months that have a signup figure.

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)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.