Go Back

Sum last n columns

In Excel, we are able to sum the last n columns of any data set by using the SUM, INDEX and COLUMNS functions. The SUM function adds all given values, the INDEX function returns a value or reference to a cell, while COLUMNS returns the number of columns in an array. This step by step tutorial will assist all levels of Excel users in finding the sum of the last n columns.    

Figure 1. Final result: Sum last n columns

Final formula: =SUM(INDEX(C3:F5,0,COLUMNS(C3:F5)-(I2-1)):INDEX(C3:F5,0,COLUMNS(C3:F5)))

Syntax of SUM Function

=SUM(number1,[number2],...])

  • number1 – any number, array or cell reference whose values we want to add
  • Only number1 is required; succeeding numbers are optional

Syntax of INDEX function

=INDEX(array, row_num, column_num)

The parameters are:

  • array – a range of cells where we want to retrieve some data
  • row_num – the row in the array from which we want to retrieve data
  • column_num – the column in the array from which we want to retrieve data

Syntax of COLUMNS Function

=COLUMNS(array)

  • array – reference to a range of cells whose number of columns we want to determine

Setting up Our Data

Our table consists of Product ID (column B) and the quarterly sales in columns C, D, E and F.  Cell I2 contains the number of columns that correspond to the last n quarters we want to sum. The resulting sum will be in cell I3.

Figure 2. Sample data to sum last n columns

Sum last n columns

To sum the sales for the last two quarter, we will follow these steps:

Step 1.  Select cell I3

Step 2.  Enter the formula: =SUM(INDEX(C3:F5,0,COLUMNS(C3:F5)-(I2-1)):INDEX(C3:F5,0,COLUMNS(C3:F5)))

Step 3.  Press ENTER

It is important to note that in the INDEX formula, we set the row number to zero “0” in order to lookup the entire column.  

Figure 3. Entering the formula to sum the last two columns

Array C3:F5 is the data range that we have.  “COLUMNS(C3:F5)” returns 4, because there are four columns in the data range.  “I2-1” returns 1 because “I2” is equal to 2.

Hence, the SUM formula is simplified and shown as an array of two INDEX formulas.  

=SUM(INDEX(C3:F5,0,3):INDEX(C3:F5,0,4))

Finally, the formula sums the third and fourth column of the range C3:F5, resulting to $4,100.  We have now successfully obtained the sum of the last two columns of our data set.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I am trying to use the following formula to sum a column of six items if any item is nonzero but show nothing if they are all zero. If the first, second or sixth item is nonzero, it works. But if the those three are zero, the sum ignores the third, fourth, and fifth items. Here is the formula: =IF(N(F56)*N($J$2)+N(F57)*N($J$3)+N(F58)*N($J$4)+N(F59)*($J$5)+N(F60)*N($J$6)+N(F61)*N($J$7)=0,"",SUM(F56:F61))
Solved by C. Q. in 12 mins
I have define 3 ranges per cell as: 1 = Satisfactory 0= Unaceptable N/A= No Applicable I want to SUM the cells and when the SUM of all cells is N/A I want to set up as 100%, if the sum of all cells is 1 and if the sum of al cells is "0" provide me 0%. Permits/Authorizations Used 100% 0101 Part A Work details N/A 0102 Part B Work scope & description N/A 0103 Part C Specialty safety equipment 1 0104 Part D Safety precautions N/A
Solved by I. S. in 25 mins
I have two people with the same last name (VanNorstran). One spells the last name with the capital second N, and the second spells it without the capital N (Vannorstran). How can I get excel to recognize they are two distinct entries?
Solved by S. B. in 13 mins

Leave a Comment

avatar