Lamisa Musharrat
Doing SUM in Excel is a common thing, but sometimes you want to SUM the final 5 values in row. For a big dataset, this can be a gruesome thing to do. So, we are going to show how to SUM the last 5 values in a row in Excel in five unique ways.
📁 Download Excel File
Download the Excel file we used to create this article so you can practice.
Learn to SUM Last 5 Values in Row with These 5 Suitable Approaches
As we have mentioned earlier we are going to sum the last 5 values in a row using 5 unique ways, in order to do so, we have created a dataset to demonstrate the ways. This is a dataset containing the marks distribution of six high school students. You can see this dataset has seven rows that contain the student’s marks, but we are going to sum the final 5 values in row only.
1. Applying COUNTIF Function
The COUNTIF function of Excel is used to count the number of cells based on the criterion.
We will use this function here but it will be combined with other functions. Follow the steps to know more.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select a cell where you will enter the function to see the summation.
- We have selected cell J6.
- Now, insert the given formula in your specified cell.
The formula is given below with a breakdown.
🔨 Formula Breakdown
IF(COUNTIF($C6:$I6,”>0″)>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),”< 5 Values”)
👉 Here, IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1
- $C6:$I6>0 is the logical_test COLUMN($C7:$I7)-COLUMN($C7)+1 represents the [value_if_true] argument. So this portion of the function returns the value {1,2,3,4,5,6,7}.
- As a result,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}) will become LARGE({1,2,3,4,5,6,7}-1,{1,2,3,4,5}).
- So, it will give the value {6,5,4,3,2}.
👉 Then, OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5})) turns OFFSET($C6,0,{6,5,4,3,2}).
- Here, $C6 is the given reference.
- 0 denotes the rows.
- {6,5,4,3,2} is used as a cols argument.
- So, the final value that it will return is {500,450,450,425,400}.
👉 After that, SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))) becomes SUBTOTAL(9,{70,60,90,60,70}).
- 9 indicates the function_num.
- {70,60,90,60,70} is for the ref1.
- So, the final value from this becomes {70,60,90,60,70}.
👉 Again, LARGE(SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5}) becomes LARGE({70,60,90,60,70},{1,2,3,4,5}). And eventually gives the output {70,60,90,60,70}.
👉 Therefore, SUM(LARGE(SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})) turns SUM({70,60,90,60,70}). And will give an output of 350.
👉 Then, COUNTIF($C7:$I7,”>0″) gives the output 7.
👉 So to sum up, IF(COUNTIF($C6:$I6,”>0″)>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C6,0,LARGE(IF($C6:$I6>0,COLUMN($C6:$I6)-COLUMN($C6)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),”< 5 Values”) becomes IF(7>=5,350,”< 5 Values”).
- where,7>=5 indicates logical_test.
- 350 is the [value_if_true].
- “< 5 Values” indicates the [value_if_false].
- So the final value is 350.
- Enter the formula and you will be able to see the summation in the specific cell.
- Now to fill other cells in the same column, drag the Fill Handle icon down.
- So, all the blank cells will be filled.
📕 Read More: 3 Quick Approaches to Sum Top N Values in Excel
2. Combining INDEX and MATCH Functions
By integrating INDEX and MATCH functions we can solve this. The INDEX function provides values that are within a specified cell reference. On the other hand, the MATCH function helps to search for a specified item within a cell range and give the relative position of that item as an output.
Follow the steps below to know more.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select a cell where you will insert the function to see the output.
- We have specified cell J6.
- Now, enter the following formula in your specified cell.
The formula is given below with an explanation.
Here, $C6:$I6 is the range of marks from different subjects per student. Another element of this formula that might surprise you is 9.9E+307 which is a large number and it is utilized here to get the largest number which can be found by integrating the different parts of the formula. Again, -4 is used because we are estimating the final five values of the dataset.
🔨 Formula Breakdown
SUM(INDEX($C6:$I6,MATCH(9.9E+307,$C6:INDEX($C6:$I6,MATCH(9.9E+307,$C6:$I6)-4))):$I6)
👉 Here, MATCH(9.9E+307,$C6:$I6)
- $C6:$I6 indicates the lookup_array.
- 9E+307 is for lookup_value argument.
- These return the value 7.
👉 Then INDEX($C6:$I6,MATCH(9.9E+307,$C6:$I6)-4) turns to INDEX($C6:$I6,7-4).
- The array argument here is $C7:$I7.
- The row_num argument is presented by 7-4.
- And finally, it returns the value 70.
👉 As a result, the function, MATCH(9.9E+307,$C6:INDEX($C6:$I6,MATCH(9.9E+307,$C6:$I6)-4)) turns MATCH(9.9E+307,$C6:70).
- Which bears an output of 3.
👉 Then, function INDEX($C6:$I6,MATCH(9.9E+307,$C7:INDEX($C6:$I6,MATCH(9.9E+307,$C6:$I6)-4))) transforms to INDEX($C6:$I6,3).
- And finally, it gives the output value of 70.
👉 So, the function as a whole SUM(INDEX($C7:$I7,MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4))):$I7) turns SUM(70:$I6).
- And so, it gives the output 350.
- Now press Enter from your keyboard and see the result in your specific cell.
- Now, drag the Fill Handle icon down to fill other cells in the same column.
- All the blank cells will be filled.
📕 Read More: 4 Easy Ways to Add a Range of Cells in Excel
3. Using LARGE and COLUMN Functions
The LARGE function as its name indicates returns the largest value in a data set. Again, the COLUMN function returns the column numbers of a given cell. We can combine these two functions, to sum up, the last five rows of a dataset. Follow the steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- First, you have to do is select a cell where you will enter the function to see the summation. We have selected cell J6.
- After that, type the given formula in your preferred cell.
🔨 Formula Breakdown
SUM((COLUMN($C6:$I6)>=LARGE(COLUMN($C6:$I6)*(N(+$C6:$I6)>0),5))*N(+$C6:$I6))
👉 The function N is used in this case to turn the cell value into a number.
👉 The column number for the final five subject’s marks is specified using the COLUMN function.
👉 In the function COLUMN($C6:$I6)
- $C6:$I6 refers to the [reference].
- So, it gives the output {3,4,5,6,7,8,9}.
👉 Again, the function LARGE(COLUMN($C6:$I6)*(N(+$C6:$I6)>0),5) is now LARGE({3,4,5,6,7,8,9},5).
- where, {3,4,5,6,7,8,9} represents the array.
- 6 is the given k argument.
- So it returns the value 5.
👉 Therefore, COLUMN($C6:$I6) gives {3,4,5,6,7,8,9}
👉 Due to this,(COLUMN($C6:$I6)>=LARGE(COLUMN($C6:$I6)*(N(+$C6:$I6)>0),5))*N(+$C6:$I6) gives ({3,4,5,6,7,8,9}>=5)*{60,55,70,60,90,60,70}.
- So the Output becomes {0,0,70,60,90,60,70}.
👉 In the final step,SUM((COLUMN($C6:$I6)>=LARGE(COLUMN($C6:$I6)*(N(+$C6:$I6)>0),5))*N(+$C6:$I6)) turns SUM({0,0,70,60,90,60,70}).
- So the final value becomes 350.
- Finally, press Enter from your keyboard and see the result in your specific cell.
- Now, drag the Fill Handle icon down to fill other cells in the same column.
- All the blank cells will be filled.
4. Applying VBA Code
Maybe you are familiar with the VBA code in Excel. In this tutorial, we are going to determine the sum of the last 5 values in a row using the VBA feature of Excel. Let’s do it with the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the Developer option from the toolbar. And from there click the Visual Basic option to open.
- After opening the Microsoft Visual Basic for Applications, click the Insert and then click the Module.
- In this new module, you have to write the code given below
Function sum_last_5_Values_in_Row(input_cells As Range, _
Optional ByVal l_count As Long = 5) As Double
Dim a As Long, b As Long, sumX As Double
On Error GoTo err_hdl
a = input_cells.Count
Do While l_count > 0
If input_cells(a) <> "" Then
sumX = sumX + input_cells(a)
l_count = l_count - 1
End If
a = a - 1
Loop
function_exit: sum_last_5_Values_in_Row = sumX
Exit Function
err_hdl: Resume function_exit
End Function- Now you have to insert the following formula in that cell where you want to see the result of the sum.
- Here, we have defined the sum_last_5_Values_in_Row in our VBA code. Also, the range C6:I6 is the range of the row where marks are assigned.
- Finally, you will be able to see the result of the summation in your selected cell.
📕 Read More: 7 Easy Ways to Sum Colored Cells in Excel Without VBA
5. Utilizing OFFSET Function
Among all these techniques mentioned earlier in this article may seem a bit difficult. But don’t worry we are going to introduce the simplest way to do this using the built-in OFFSET Function. Go through the steps below and repeat this process with us.
⬇️⬇️ STEPS ⬇️⬇️
- First, specify a cell where you will insert the function to see the output.
Here, we have specified cell J6.
- Now you have to Enter the formula given below.
🔨 Formula Breakdown
SUM(OFFSET($C6,0,COUNT($C6:$I6)-1,,-5))
👉 The COUNT function is used here to count the number of columns from the first column.
- Here, the output will be 7 as there are 7 columns containing the marks of the students.
👉 Again, OFFSET($C6,0,COUNT($C6:$I6)-1,,-5) will become OFFSET($C6,0,7-1,,-5).
- Here,$C6 is the reference.
- 0 represents the rows argument.
- 7-1 is to refer the cols argument.
- -5 is for the [width] argument.
- So the output will be {70,60,90,60,70}.
👉 Then, the sum of the supplied numbers will then be returned by the SUM function.
👉 Finally, =SUM(OFFSET($C6,0,COUNT($C6:$I6)-1,,-5)) will be SUM({70,60,90,60,70}).
- And so, the output will be 350.
- And in this final step, press Enter from your keyboard and see the result in your specific cell.
- Now, drag the Fill Handle icon down to fill other cells in the same column.
- As a result, all the blank cells will be filled.
📝 Takeaway from This Article
📌 After reading this article you will have an idea of how to do the summation of the final 5 cells in a row using different functions and their combination.
📌 VBA code that was demonstrated here can be used to solve this problem quickly.
Conclusion
Excel have a huge number of functions to make your work easier, you just have to know their uses. This article has given the idea to solve a task in five unique ways. Choose your approach whichever seems fit for you. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.
