If your list keeps growing and you only want to total the most recent few entries, TAKE makes this pretty simple.

For example:

Date	Sales
Sep 1	820
Sep 2	1,150
Sep 3	960
Sep 4	1,320
Sep 5	780
Sep 6	1,410
Sep 7	1,080
Sep 8	1,260

To sum the last 5 sales values:

=SUM(TAKE(B2:B9,-5))

Result:

5,850

The negative 5 tells TAKE to start from the bottom of the range and return the last five values. SUM then adds them together.

Let a cell control N

If you don’t want to edit the formula every time, put the number of rows you want to include in another cell.

For example, if D2 contains:

5

use:

=SUM(TAKE(B2:B100,-D2))

Now you can change D2 to 3, 7, 12, or whatever you need, and the total updates automatically.

Return the last N values instead

You don’t have to sum them. If you just want to pull out the most recent values, use:

=TAKE(B2:B100,-D2)

The last N values will spill into the cells below.

Take the last N rows from a table

You can also use TAKE on multiple columns.

For example:

=TAKE(A2:C100,-5)

This returns the last 5 rows from all three columns, which is handy when you want to show the most recent records rather than just calculate a total.

Note: TAKE is available in Microsoft 365 and Excel 2024.

Hope this helps! Feel free to join our community at r/ExtendOffice and share your Excel tips, questions, or solutions with others.