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.

**Contents**hide

## 📁 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.

**Approach**

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

**=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”)**

**🔨 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**

**Approach**

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

**=SUM(INDEX($C6:$I6,MATCH(9.9E+307,$C6:INDEX($C6:$I6,MATCH(9.9E+307,$C6:$I6)-4))):$I6)**

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
argument here is*array***$C7:$I7**. - The
argument is presented by*row_num***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**

**Approach**

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

**=SUM((COLUMN($C6:$I6)>=LARGE(COLUMN($C6:$I6)*(N(+$C6:$I6)>0),5))*N(+$C6:$I6))**

**🔨 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.

**Approach**

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

**=sum_last_5_Values_in_Row(C6:I6)**

- 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**

**Approach**

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

**=SUM(OFFSET($C6,0,COUNT($C6:$I6)-1,,-5))**

**🔨 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.