Sum last n columns

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.


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

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

 Sum last n columns 1

Figure 1. Form a table with Respective fields

  1. 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.
  2. 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).
  3. 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).
  4. 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.

 Sum last n columns  2Figure 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).
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment