How to Sum Last 5 Values in a Row in Excel (5 Handy Approaches)

Get FREE Advanced Excel Exercises with Solutions!

We frequently add up a series of numbers, but in certain cases, we may need to sum the last 5 values in a row in Excel. In this article, I’ll discuss the five productive ways, including the VBA code to sum the last 5 values in a row.


Download Practice Workbook


5 Simple Methods to Sum Last 5 Values in a Row in Excel

Let’s have a glimpse at the following dataset. Here, Sales of five items are provided from 2015 to 2021. We have to find the sum of the sales from 2017 to 2021.

excel sum last 5 values in row

Let’s get started.


1. Using the OFFSET Function

The most popular method of finding the sum of the last 5 values in a row in Excel is to use the OFFSET function.

The function returns a cell or cell range where the starting point, number of rows and columns, height, and width are provided.

Now, enter the following formula in cell J7.

=SUM(OFFSET($C7,0,COUNT($C7:$I7)-1,,-5))

Here, C7 (the sales of AC in 2015) is the starting point, and the number of rows is 0 because the sales of AC over 7 years are in the cell of the starting point(C7).

Formula Breakdown

  • Firstly, the COUNT function is used in the formula to count the number of columns from the starting point.
    • Output 7.
  • Now, OFFSET($C7,0,COUNT($C7:$I7)-1,,-5) becomes OFFSET($C7,0,7-1,,-5).
    • $C7 → It is the reference argument.
    • 0 → This indicates the rows argument.
    • 7-1 →  This refers to the cols argument.
    • -5 → It is the [width] argument.
    • Output →  {400,425,450,450,500}.
  • Finally, the SUM function will return the sum of the specified values.
  • SUM(OFFSET($C7,0,COUNT($C7:$I7)-1,,-5)) becomes SUM({400,425,450,450,500}).
    • Output $2,225.

Using the OFFSET Function sum the last 5 values in a row in Excel

Read More: How to Sum Multiple Rows and Columns in Excel


2. Utilizing INDEX and MATCH Functions

Basically, this function is a combination of the INDEX and MATCH functions.

The INDEX function in Excel returns the value that is located at a specified place in a range or array. The syntax of the function is

The MATCH function is used for locating the search value location in a row, column, or table. The function is often coupled with the INDEX function to retrieve a corresponding value.

Now, use the below formula in cell J7.

=SUM(INDEX($C7:$I7,MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4))):$I7)

In this formula, C7:I7 is the cell range for the sales of AC over years, C7 is the sales of AC in 2015, and I7 is the sales of AC in 2021.

Besides, 9.9E+307 which is mainly 9.999999999999E+307 is an extremely huge number and it is used to find the greatest number that can be obtained by combining the other parts of the formula.

Lastly, -4 is because we are finding the last 5 values.

Formula Breakdown

  • The 1st MATCH function is MATCH(9.9E+307,$C7:$I7).
    • 9.9E+307 → It is the lookup_value argument.
    • $C7:$I7 → This represents the lookup_array argument.
    • Output → 7.
  • Now,the 1st INDEX function, INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4) becomes INDEX($C7:$I7,7-4).
    • $C7:$I7 → It is the array argument.
    • 7-4 → This indicates the row_num argument.
    • Output → 400.
  • The 2nd MATCH function is MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4)) and it becomes MATCH(9.9E+307,$C7:400).
    • Output → 3.
  • Then, the 2nd INDEX function, INDEX($C7:$I7,MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4))) becomes INDEX($C7:$I7,3),
    • Output → 400.
  • Finally, SUM(INDEX($C7:$I7,MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4))):$I7) becomes SUM(400:$I7)B.
    • Output → $2,225.

Utilizing INDEX, and MATCH Functions sum the last 5 values in a row in Excel

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


3. Applying LARGE and COLUMN Functions

You can utilize the LARGE and COLUMN functions combined to find the sum of the last 5 values in a row in Excel.

The LARGE function returns numerical values according to their position in a list when they are sorted by value. The syntax of the function is

Moreover, the COLUMN function finds the column number for a reference cell.

Now, let’s apply the following formula in cell J7.

=SUM((COLUMN($C7:$I7)>=LARGE(COLUMN($C7:$I7)*(N(+$C7:$I7)>0),5))*N(+$C7:$I7))

Here, C7:I7 is the cell range for the sales of AC over the years.

Formula Breakdown

  • Here, the N function is used to convert the cell value into a number.
  • Then, the COLUMN function here specifies the column number for the last 5 years’ sales.
    • COLUMN($C7:$I7) is the 1st COLUMN function.
    • $C7:$I7 → It is the [reference] argument.
    • Output {3,4,5,6,7,8,9}.
  • After that, LARGE(COLUMN($C7:$I7)*(N(+$C7:$I7)>0),5) becomes LARGE({3,4,5,6,7,8,9},5).
    • Here, {3,4,5,6,7,8,9} → This indicates the array argument.
    • 6 → It is the k argument.
    • Output 5.
  • Now, the 2nd COLUMN function COLUMN($C7:$I7) returns {3,4,5,6,7,8,9} as Output.
  • As a result, (COLUMN($C7:$I7)>=LARGE(COLUMN($C7:$I7)*(N(+$C7:$I7)>0),5))*N(+$C7:$I7) becomes ({3,4,5,6,7,8,9}>=5)*{300,350,400,425,450,450,500}.
    • Output {0,0,400,425,450,450,500}.
  • Finally, SUM((COLUMN($C7:$I7)>=LARGE(COLUMN($C7:$I7)*(N(+$C7:$I7)>0),5))*N(+$C7:$I7)) becomes SUM({0,0,400,425,450,450,500}).
    • Output  $2,225.

Read More: All the Easy Ways to Add up (Sum) a column in Excel


Similar Readings


4. Using COUNTIF Function

Do you know about the COUNTIF function?

The function can count cell values, even dates, numbers, or text. We’ll use the function here.

Again, the SUBTOTAL function returns an aggregate result for provided values.

Now, apply the following formula in cell J7.

=IF(COUNTIF($C7:$I7,">0")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),"< 5 Values")

Here, C7:I7 is the cell range for the sales of AC over years, C7 is the sales of AC in 2015.

Formula Breakdown

  • The 1st IF function IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1).
    • $C7:$I7>0 → It is the logical_test function.
    • COLUMN($C7:$I7)-COLUMN($C7)+1 → This indicates the [value_if_true] argument.
    • Output → {1,2,3,4,5,6,7}.
  • Now, LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}) becomes LARGE({1,2,3,4,5,6,7}-1,{1,2,3,4,5}).
    • Output {6,5,4,3,2}.
  • After that, the OFFSET function, OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5})) becomes OFFSET($C7,0,{6,5,4,3,2}).
    • $C7 → It is the reference argument.
    • 0 → This represents the rows argument.
    • {6,5,4,3,2} → It indicates the cols argument.
    • Output {500,450,450,425,400}.
  • Then, SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))) becomes SUBTOTAL(9,{500,450,450,425,400}).
    • 9 → It refers to the function_num argument.
    • {500,450,450,425,400} → This indicates the ref1 argument.
    • Output {500,450,450,425,400}.
  • Now, LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5}) becomes LARGE({500,450,450,425,400},{1,2,3,4,5}).
    • Output {500,450,450,425,400}.
  • Following that, SUM(LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})) becomes SUM({500,450,450,425,400}).
    • Output 2225.
  • Now, COUNTIF($C7:$I7,”>0″) returns 7 as output.
  • Finally, IF(COUNTIF($C7:$I7,”>0″)>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),”< 5 Values”) becomes IF(7>=5,2225,”< 5 Values”).
    • 7>=5 → It is the logical_test argument.
    • 2225 → It indicates the [value_if_true] argument.
    • “< 5 Values” → This refers to the [value_if_false] argument.
    • Output $2,225.

Using COUNTIF Function sum the last 5 values in a row in Excel

Read More: How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)


5. Employing VBA Code

If you have a larger dataset, it is time-consuming and a little bit tiring to get the required result using a formula.

Rather, you can utilize the VBA code in Excel which performs the result rapidly and accurately.

Now, let’s see how you can apply the VBA code to our dataset.

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

Then, have a look at the code for finding the sum of the last 5 sales for AC items.

Function sum_last_5(input_cells As Range, _
Optional ByVal l_count As Long = 5) As Double
Dim x As Long, y As Long, sum As Double
On Error GoTo err_hdl
x = input_cells.Count
Do While l_count > 0
If input_cells(x) <> "" Then
sum = sum + input_cells(x)
l_count = l_count - 1
End If
x = x - 1
Loop
function_exit: sum_last_5 = sum
Exit Function
err_hdl: Resume function_exit
End Function

Code Breakdown

  • Firstly, we initiated function named sum_last_5.
  • Inside the function arguments, we declared a variable input_cell as Range.
  • We assigned the output data type of the function as Double.
  • Then, we declared 3 variables.
  • After that, we used an On Error statement to enable an error-handling routine.
  • Now, we assigned the Count of input_cells in variable x.
  • Following that, we initiated a Do While loop.
  • Next, we used an IF statement to check whether the input_cells are blank or not.
  • Then, we added the input_cells variable with the sum variable and again assigned it back to the sum variable.
  • Now, we subtracted 1 from the l_count variable and again assigned it back to the l_count variable.
  • After that, we ended the IF statement.
  • Afterward, we subtracted 1 from the variable x and again assigned it back to the variable x.
  • Then, we closed the Do While loop.
  • Subsequently, we specified the exit conditions for the function.
  • Finally, we terminated the function.

Now, use the formula given below in cell J7.

=sum_last_5(C7:I7)

Consequently, you will have the following outputs.

Read More: How to Add Multiple Cells in Excel (6 Methods)


How to Average Last n Values in a Row in Excel

In this section of the article, we will learn how we can average the last n number of values in a row in Excel. Here, n can be any given number based on the size of the dataset. Let’s say, we have a value of n as 4. Our aim is to calculate the Average of the last 4 values in a row. So, let’s follow the steps mentioned below to do this.

Steps:

  • Firstly, use the following formula in cell J7.
=AVERAGE(OFFSET(C7,0,COUNT(C7:I7)-1,,-$C$13))
  • Following that, press ENTER.

Formula Breakdown 

  • Firstly, the COUNT function, COUNT(C7:I7) returns 7 as output.
  • Now, OFFSET(C7,0,COUNT(C7:I7)-1,,-$C$13) becomes OFFSET(C7,0,7-1,,-$C$13).
    • Output {425,450,450,500}.
  • Finally, the AVERAGE function will return the average of the specified numbers.
  • AVERAGE(OFFSET(C7,0,COUNT(C7:I7)-1,,-$C$13)) becomes AVERAGE({425,450,450,500}).
    • Output $456.

  • Finally, use the AutoFill option of Excel to get the remaining outputs as demonstrated in the following image.

Final output of method 6 to Average Last n Values in a Row in Excel


How to Sum Every 3 Cells in Excel

Now, we will discuss how we can sum every 3 cells in Excel. It comes in particularly handy while calculating Quarterly computations. Let’s say, we have the monthly sales data for different Items for a store. Our goal is to calculate the Quarterly Sales of the Items. Let’s use the steps outlined below to do this.

Steps:

  • Firstly, you can use the following formula in cell C20.
=SUM(OFFSET($H$6,(ROW()-ROW($C$20))*3,0,3,1))

Here, cell H6 is the Monthly Sales for the month of January, and cell C20 indicates the cell of Q1 Sales:

  • Then, hit ENTER.

Formula Breakdown 

  • Firstly, the ROW function, ROW($C$20) returns {20} as output.
  • Now, OFFSET($H$6,(ROW()-ROW($C$20))*3,0,3,1) becomes OFFSET($H$6,({20}-{20})*3,0,3,1).
  • Finally, the SUM function will return the sum of the 3 cells.
    • Output $13,482.

  • Lastly, use the AutoFill feature of Excel to have the rest of the Quarterly Sales values.

Final output of method 7 to Sum Every 3 Cells in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to sum the last 5 values in a row in Excel


Conclusion

This is how you may calculate the sum of the last 5 values using the formula and the VBA code. If you have any confusion or suggestions, please let me know in the comments section below.

Thanks for being with me.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo