Sum first n columns
This tutorial shows how to add up the first n columns from a specific range using an Excel formula or VBA
Example: Sum last n columns
|
This formula uses the Excel SUM and INDEX functions to sum the first n number of columns with selected range that captures the headings. In this example the formula will sum the first two columns from range B6:E13, therefore it will sum the values that are captured in C6 to D13.
|
METHOD 2. Sum last n columns without headings using Excel formula
EXCEL
|
This formula uses the Excel SUM and INDEX functions to sum the first n number of columns with selected range not capturing the headings. In this example the formula will sum the first two columns from range C7:E13, therefore it will sum the values that are captured in C7 to D13.
|
Sub Sum_first_n_columns()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'sum first 2 columns in a range B6:E13 with the headings being selected in the range
ws.Range("H6").Formula = "=SUM(INDEX(B6:E13,0,1):INDEX(B6:E13,0,C4+1))"
ws.Range("H6").Formula = "=SUM(INDEX(B6:E13,0,1):INDEX(B6:E13,0,C4+1))"
End Sub
Sub Sum_first_n_columns()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'sum first 2 columns in a range C7:E13, without the heading being selected in the range
ws.Range("H6").Formula = "=SUM(INDEX(C7:E13,0,1):INDEX(C7:E13,0,C4))"
ws.Range("H6").Formula = "=SUM(INDEX(C7:E13,0,1):INDEX(C7:E13,0,C4))"
End Sub
EXPLANATION
This tutorial shows how to sum the first n number of columns in a range using Excel formulas or VBA.
This tutorial provides two Excel and VBA methods that can be used to sum the first n columns from a range.
The first method should be applied where the range captures the headings and the second method should be applied where the range does not capture the headings.
The first method should be applied where the range captures the headings and the second method should be applied where the range does not capture the headings.
FORMULA (with headings)
=SUM(INDEX(range,0,1):INDEX(range,0,n_num+1))
=SUM(INDEX(range,0,1):INDEX(range,0,n_num+1))
FORMULA (without headings)
=SUM(INDEX(range,0,1):INDEX(range,0,n_num))
=SUM(INDEX(range,0,1):INDEX(range,0,n_num))
ARGUMENTS
range: The range from which you want to sum the first 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 first 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 last n columns | How to add up the last n columns from a specific range using Excel and VBA | |
| Sum last n rows | How to add up the last n rows from a specific range 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 |