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.

**Table of Contents**hide

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

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 theargument.*reference***0**→ This indicates theargument.*rows***7-1**→ This refers to theargument.*cols***-5**→ It is theargument.*[width]***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**.

**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 theargument.*lookup_value***$C7:$I7 →**This represents theargument.*lookup_array***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 theargument.*array***7-4**→ This indicates theargument.*row_num***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**.

**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 theargument.*[reference]***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 theargument.*array* **6**→ It is theargument.*k***Output**→**5**.

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

**How to Add Rows in Excel with Formula (5 ways)****3 Easy Ways to Sum Top n Values in Excel****How to Sum Filtered Cells in Excel (5 Suitable Ways)****Sum Cells in Excel: Continuous, Random, With Criteria, etc.****How to Sum Selected Cells in Excel (4 Easy Methods)**

### 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 thefunction.*logical_test***COLUMN($C7:$I7)-COLUMN($C7)+1**→ This indicates theargument.*[value_if_true]***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 theargument.*reference***0**→ This represents theargument.*rows***{6,5,4,3,2}**→ It indicates theargument.*cols***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 theargument.*function_num***{500,450,450,425,400}**→ This indicates theargument.*ref1***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 theargument.*logical_test***2225**→ It indicates theargument.*[value_if_true]***“< 5 Values”**→ This refers to theargument.*[value_if_false]***Output**→**$2,225**.

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

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

## Practice Section

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

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

**How to Sum Between Two Numbers Formula in Excel****Shortcut for Sum in Excel (2 Quick Tricks)****How to Sum Colored Cells in Excel (4 Ways)****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)****Sum All Matches with VLOOKUP in Excel (3 Easy Ways)****Excel Sum If a Cell Contains Criteria (5 Examples)**