The Sum function of Excel is the math and trigonometric functions that add values.

## What is a Sum last n column in Excel?

In Excel, the Sum function allows users to add individual values, cell references, or ranges and also a mix of all three arguments.

## How the Sum last n column is used in Excel?

The Sum function is used with the Index function to calculate the n column value of the data. To calculate the Sum last n column, users will have to use three functions together, i.e. SUM, INDEX, and COLUMNS. The proper use of the formula is explained in the example.

## Formula or Syntax

The formula used for the Sum last n column is given below:

`=SUM (INDEX (data, 0, COLUMNS (data)-(n-1)): INDEX (data, 0, COLUMNS (data)))`

## Parameters or Arguments of Sum last n column

The arguments that must be passed for the functions to be applied are listed below:

- Data: A reference to the range of cells. It can be array, array formula, or a single contagious group
- Row_num: The row position in the array.
- Col_num: The column position in the array.
- Area_num: The range in reference that can be used.
- Number1: The item on which SUM function is to be applied.
- Number2: The second item to sum.

**Example:**

Now, let us take an example to understand the working of the Sum last n column function.

- Make a table of certain fields like months and sale of some products in that month. We will take more than 2 products to understand the example more clearly.

Figure 1. Form a table with Respective fields

- We will try to sum the last n column in the table above, i.e. last 3 columns, or last 4 columns, etc. For this, we will use the formula based on INDEX function and will use COLUMNS function too. As the INDEX function can be used to return a reference to entire rows and columns.
- We will build the reference in two parts and will join it by the range operator. So to get the reference to a left column, we will use the
`INDEX (data, 0, COLUMNS (data)-(K4-1))`

. As the data we formed contains only 6 columns, and K4 contains 3, the reference will be simplified as INDEX (data, 0, 4). - To get the reference for the right column, we will use INDEX (data, 0, COLUMNS (data)). This will return the reference to column 6 of the data range, and the COLUMN function will become 6. There, the formula will be INDEX (data, 0, 6).
- Now, join the INDEX function and form one single formula. Together, the formula will return a reference to the column number 4 through 6, i.e. F5: H8. So, the function will sum up the values as
`SUM ({15;14;10;9;12;12;7;9;9;12;13;13})`

. The SUM function will return the sum of last n columns as 135.

Figure 2. Return Value of the Sum last n column function

## Note on Usage of Sum last n column

The note on usage of SUM last n column is given below:

- The SUM function can sum up to 255 arguments.
- The argument in the function can be passed as numbers, cell references, ranges, arrays, constants, and as the results of formula and functions.
- The representation is added up
`SUM (C1: C3)`

all the cells C1 through C3, i.e. you will have the values which is equivalent to`SUM (C1, C2, C3)`

.

## Leave a Comment