Excel Sum Last 5 Values in Row (Formula + VBA Code)

Using COUNTIF Function

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

Excel Sum Last 5 Values in Row

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.

Dataset

Let’s get started.

1. Using the OFFSET Function

The most popular method of finding the sum of the last 5 values 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.

The syntax of the function is

=OFFSET (reference, rows, cols, [height], [width])

The arguments are-

reference – The starting point, given as a cell reference or range.

rows – The number of rows to offset below the starting reference.

cols – The number of columns to offset to the right of the starting reference.

height – The height in rows. It is optional.

width – The width in columns. Also, it is optional.

Let’s apply the function along with the COUNT and SUM functions for our dataset. So, the formula takes the following form.

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

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

Besides, the COUNT function is used in the formula to count the number of columns from the starting point.

Lastly, -1 is the height as the formula is in the reference point and -5 is the width as we are finding the last 5 values.

Using OFFSET Function

2. Utilizing INDEX MATCH Function

Basically, this function is the 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

=INDEX (array, row_num, [col_num], [area_num])

The arguments are-

array – A range of cells, or an array constant.

row_num – The row position in the reference or array.

col_num – [optional] The column position in the reference or array.

area_num – [optional] The range in reference that should be used.

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.

The syntax of the function is

=MATCH (lookup_value, lookup_array, [match_type])

The arguments are-

lookup_value – The value to match in lookup_array.

lookup_array – A range of cells or an array reference.

Now, see the below formula for our dataset.

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

In this formula, C6:I6 is the cell range for the sales of AC over years, C6 is the sales of AC in 2015, and I6 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.

Using INDEX MATCH Function

3. Using LARGE & COLUMN Function

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

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

=LARGE (array, k)

The arguments are-

array – The array from which you want to select the kth largest value.

k – An integer that specifies the position from the largest value, i.e. the kth position.

Moreover, the COLUMN function finds the column number for a reference cell. The syntax of the function is

=COLUMN ([reference])

The only argument is

reference – A reference to a cell or range of cells.

Let’s see the application of the above function for determining the last 5 sales.

The formula is

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

Here, C6:I6 is the cell range for the sales of AC over years.

Besides, the N function is used to convert the cell value into a number.

More importantly, the COLUMN function here specifies the column number for the last 5 years’ sales.

Finally, LARGE and SUM functions calculate the specified value.

Using LARGE & COLUMN Functions

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.

The syntax of the function is

=SUBTOTAL (function_num, ref1, [ref2], ...)

The arguments are-

function_num – A number that specifies which function to use in calculating subtotals within a list. See table below for full list.

ref1 – A named range or reference to subtotal.

ref2 – [optional] A named range or reference to subtotal.

=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, C6:I6 is the cell range for the sales of AC over years, C6 is the sales of AC in 2015.

Besides, the 9 in the SUBTOTAL function refers to the sum.

You’ll get the following output.

Using COUNTIF Function

5. 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 in 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.

Sub Sum_last_n_values_in_a_row()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Sum Last 5 Values")
'sum the last five values in a row
ws.Range("K6") = Application.Sum(Range("C6").Offset(0, Application.Count(Range("C6:I6")) - ws.Range("J6")).Resize(1, Range("J6")))
End Sub

Here, you have to fix the following kinds of stuff while inputting the code in your dataset.

  • Worksheet name: normally you have a specified worksheet name. I use “Sum Last 5 Values”
  • The output cell: Specify the output cell in the range. Here, K6 is the output cell.
  • The first cell: You have to input the first cell of your dataset. C6 is the first cel here.
  • Row range: Also fix the row range for your dataset. My row range is C6:I6
  • Last 5 values: Finally, you have to mention the number of 5 values in a cell e.g. J6 in the case of my dataset.

Using VBA Code

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo