Explanation
In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE function . In older versions of Excel you can use the OFFSET function , as explained below.
TAKE function
The TAKE function returns a subset of a given array or range . The size of the array returned is determined by separate rows and columns arguments . When positive numbers are provided for rows or columns, TAKE returns values from the start or top of the array. Negative numbers take values from the end or bottom of the array. For example:
=TAKE(array,3) // get first 3 rows
=TAKE(array,-3) // get last 3 rows
=TAKE(array,,3) // get first 3 columns
=TAKE(array,,-3) // get last 3 columns
In the worksheet shown, data is the named range C5:H16. We can retrieve the last 3 columns like this:
=TAKE(data,,-3) // last 3 columns
To make the number of columns variable, we need to swap in the reference to J5:
=TAKE(data,,-J5)
Finally, to sum the result from TAKE, we need to nest the TAKE function inside the SUM function :
=SUM(TAKE(data,,-J5))
With 3 in cell J5, the result from TAKE is the last 3 columns in data . This result is handed off to the SUM function, which returns a final result of 303, the sum of values in the range F5:H16.
Dynamic range option
One limitation of the formula above is that the named range data is static — it won’t expand as new columns are added. A simple way to make the range dynamic is to add the TRIMRANGE function to the formula like this:
=SUM(TAKE(TRIMRANGE(range,,-n)))
The TRIMRANGE function removes empty rows and columns from a range of data. The result is a “trimmed” range that only includes the used portion of the range. Because it is a formula, TRIMRANGE will update the range dynamically when data is added or removed from the original range. To make this work correctly, you will want to use a range that is largest enough to hold all possible data. For example, if the data begins in column A and might include up to 12 columns, you could use TRIMRANGE like this:
=SUM(TAKE(TRIMRANGE(A:L),,-n))
If needed, you could also remove the header row with the DROP function :
=SUM(TAKE(DROP(TRIMRANGE(A:L),1),,-3))
OFFSET function
In older versions of Excel, another way to solve this problem is to use the OFFSET function . The OFFSET function returns a reference to a range constructed with 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 3 columns in the named range data , we can use the OFFSET function like this:
=SUM(OFFSET(data,0,COLUMNS(data)-J5,,J5))
Inside the OFFSET function, we provide data for reference and 0 for rows , since we don’t want any row offset. Next, we subtract the value for n in cell J5 from the total columns in data (6) to get a value for cols (3), the column offset. We leave height empty, because OFFSET will automatically inherit the height of reference , and we supply J5 for width , since we want a 3-column range in the end. In this configuration, OFFSET returns a 3-column range starting at cell F5, and this range contains 12 rows because the named range data contains 12 rows.
Note: the OFFSET function is a volatile function and can cause performance problems in larger or more complicated worksheets. If you run into this problem, see the INDEX solution below.
INDEX function
Yet another way to solve this problem is to use the versatile INDEX function in a formula like this:
=SUM(INDEX(data,0,COLUMNS(data)-(J5-1)):INDEX(data,0,COLUMNS(data)))
The key to understanding this formula is to realize that the INDEX function can return a reference to entire rows and entire columns . To generate a reference to the “last n columns” in a table, we build a reference in two parts: the left column and the right column, then use the range operator (:) to join the two parts together. To get a reference to the left column, we use:
INDEX(data,0,COLUMNS(data)-(J5-1))
Since data contains 6 columns, the COLUMNS function returns 6, and this simplifies to:
INDEX(data,0,4) // column 4
INDEX returns a reference to column 4. For the right column in the range, we use INDEX like this:
INDEX(data,0,COLUMNS(data))
Since COLUMNS returns 6, this simplifies to:
INDEX(data,0,6) // column 6
Together, the two INDEX functions return a reference to columns 4 through 6 in the data (i.e. F5:H16). The range operator (:) joins the two references together, and the SUM function returns a final result of 303.
Explanation
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.
Making the range dynamic
One limitation of the formula explained above is that it assumes that the range going into the TAKE function contains all data. What should you do if new data is being added on an ongoing basis? One easy solution is to use the TRIMRANGE function to create a simple dynamic range like this:
=SUM(TRIMRANGE(TAKE(range,-n)))
The TRIMRANGE function removes empty rows and columns from a range of data. The result is a “trimmed” range that only includes data from the used portion of the range. Because it is a formula, TRIMRANGE will update the range dynamically when data is added or removed from the original range. To make this work correctly, you will want to use a range that is largest enough to hold all possible data. For example, if the data is in column A, you can use a full-column reference like this:
=SUM(TRIMRANGE(TAKE(A:A,-n)))
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.
Making the range dynamic
One limitation of the formulas above is that they won’t automatically include new data added to the range. In an older version of Excel, one way to make the range expand to include new data is to create a dynamic named range with a formula. You can create a dynamic named range with the OFFSET function or with the INDEX function . Another (simpler) option is to use an Excel Table to create the range.
