Sum last n rows
This tutorial shows how to add up the last n rows from a specific range using an Excel formula or VBA
Example: Sum last n rows
|
This formula uses the Excel SUM, INDEX and ROWS functions to sum the last n number of rows. In this example the formula will sum the last two rows from range C7:E13, therefore it will sum the values that are captured in C12 to E13.
|
Sub Sum_last_n_rows()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'sum last 2 rows in a range C7:E13
ws.Range("G7").Formula = "=SUM(INDEX(C7:E13,ROWS(C7:E13)-(C4-1),0):INDEX(C7:E13,ROWS(C7:E13),0))"
End Sub
EXPLANATION
This tutorial shows how to add up the last n number of rows from a specific range through the use of an Excel formula or VBA.
The Excel and VBA methods both use the SUM, INDEX and ROWS functions to return the sum of the nominated number of rows from a specific range.
FORMULA
=SUM(INDEX(range,ROWS(range)-(n_num-1),0):INDEX(range,ROWS(range),0))
=SUM(INDEX(range,ROWS(range)-(n_num-1),0):INDEX(range,ROWS(range),0))
ARGUMENTS
range: The range from which you want to sum the last n number of rows.
n_num: The number of rows that you want to sum from a selected range.
range: The range from which you want to sum the last n number of rows.
n_num: The number of rows that you want to sum from a selected range.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Sum last n columns | How to add up the last n columns from a specific range using Excel and VBA | |
| Sum last n values in a column | How to sum the last n values in a column using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUM Function | The Excel SUM function returns the sum of all numbers in a specified range | |
| INDEX Function | The Excel INDEX function returns a value that is referenced from a specified range | |
| ROWS Function | The Excel ROWS function returns the number of rows in a specified array |