How to SUM the Last N Rows in Excel

To sum the last N rows in a range, you can use the SUM function with the TAKE function or apply a workaround using the OFFSET function.

How to sum the last n rows in Excel?

Open Excel
Type =SUM(TAKE(A1:D20, -n))
Press Enter
The formula returns the last n rows.

Example

In the example, the TAKE function allows you to quickly sum the last N rows from a specific range of cells. The function supports dynamic data analysis.

Let’s see the key steps and an explanation of how the function works:

Formula:

=SUM(TAKE(C2:G13, -3))

Evaluate the formula from the inside out:

First, TAKE(C2:G13, -3) extracts the last three rows of the specified range. The first argument (C2:G13) is the data range. The second argument (-3) specifies the number of rows from the bottom to take. A negative number selects rows from the bottom, while a positive number would take rows from the top.
After that, the SUM function adds up the values returned by the TAKE function, providing the total of the last N rows. In this example, the last three rows (October, November, and December) are summed, returning a result of 814.

OFFSET Function

Okay, here is a workaround with the OFFSET function. The goal is the same as the previous example: summarize the last three rows. This function returns a range of cells with a specified number of rows and columns away from a reference point.

Formula:

=SUM(OFFSET(C2:G2,COUNT(C2:C13)-I3,0,I3))

The formula effectively grabs the data in the range C11:G13 (the green-highlighted rows in the picture represent the months of October, November, and December).

Explanation:

The OFFSET function dynamically adjusts which rows are summed based on the value in cell I3.
As n in I3 changes, the range of rows included in the sum will adjust accordingly. For example, if n were 2, the formula would sum rows November and December (two months), and if n were 1, it would only sum December.
The formula sums the last n rows from the data set (starting at row 11 for October).