## 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.

### 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.