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