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