Sum last n rows

In the example shown, we have a list of amounts in column C. The goal is to dynamically sum the last n amounts using the number that appears in cell E5 for n. Since the list may grow over time, the key requirement is to sum amounts by position. For convenience only, the values to sum are in the named range data (C5:C16). In the latest version of Excel, the best way to solve this problem is with the TAKE function, a new dynamic array function in Excel. In older versions of Excel, you can use the OFFSET function. Both approaches are explained below.

TAKE function

The TAKE function returns a subset of a given array. The number of rows and columns to return is provided by separate rows and columns arguments. For example, you can use TAKE to return the first 3 rows or columns of an array like this:

=TAKE(array,3) // first 3 rows
=TAKE(array,,3) // first 3 columns

A great feature of TAKE is that you can supply a negative value to retrieve last rows or columns:

=TAKE(array,-3) // last 3 rows
=TAKE(array,,-3) // last 3 columns

In this problem, we want to sum the last n values that appear in data, where the number of rows to return is a variable entered in cell E5. To retrieve these values, we can use TAKE like this:

TAKE(data,-E5)

With the number 6 in cell E5, TAKE will return an array with six values like this:

{1050;1150;1250;1175;1350;1325}

To sum these values, we simply need to nest the TAKE function inside the SUM function:

=SUM(TAKE(data,-E5))
=SUM({1050;1150;1250;1175;1350;1325})
=7300

The result is 7,300, the sum of the last six values in the range C5:C16. As new values are added to data, TAKE will continue to return the last n values to SUM.

OFFSET function

In older versions of Excel that do not have the TAKE function, you can use the OFFSET function to solve this problem. OFFSET is designed to create a reference for a range using five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. To sum the last 6 values in data (C5:C16), we can use the OFFSET function with the SUM function like this:

=SUM(OFFSET(C5,COUNT(data)-E5,0,E5))

Inside OFFSET, we use C5 for reference, since we want to start at C5. For rows, we use this snippet:

COUNT(data)-E5 // rows offset

Our goal is to create a range that starts at the cell in data that is n cells before last cell. The COUNT function returns the number of numeric values in data. We subtract E5 to "back up" to the correct cell. For cols, we provide 0 since we don't want a column offset. We provide E5 for height, since we want our final range to be n cells tall. We don't need to provide a value for the optional width argument, since width will inherit from reference. In this configuration, OFFSET will return a reference to C11:C16, which contains the last 6 values in data. The formula will evaluate like this:

=SUM(OFFSET(C5,COUNT(data)-E5,0,E5))
=SUM(OFFSET(C5,12-6,0,6))
=SUM(C11:C16)
=SUM({1050;1150;1250;1175;1350;1325})
=7300

The final result is 7300, the sum of the last six values in the range C5:C16.

INDEX function

One thing you might notice about the OFFSET formula above is that we are providing a reference to both data and cell C5, the first cell in data. This makes the formula more error-prone since data and C5 are disconnected. You can make the formula more robust and portable by using the INDEX function to return the first cell in data like this:

=SUM(OFFSET(INDEX(data,1),COUNT(data)-E5,0,E5))

This works because the INDEX function returns C5 as a reference, not a value. Now as long as the reference to data is correct, the formula will work properly.