Sum last n columns

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

You can read more about the TRIMRANGE function here.

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.