Sum last n values in a row
To sum the last n values in a row you can use a combination of Excel SUM, OFFSET and COUNT functions, using Excel and VBA
Example: Sum last n values in a row
|
This formula sums the last n values from a specific row, using a combination of the Excel SUM, OFFSET and COUNT functions. In this example the formula sums the last three values (the number is captured in cell I5 and referenced in the formula) in row 4.
Please note that if there is a blank cell or a non numeric value in the row the formula will take this cell into consideration and will apply a value of 0 against the cell. |
Sub Sum_last_n_values_in_a_row()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'sum the last three values in a row
ws.Range("J5") = Application.Sum(Range("C4").Offset(0, Application.Count(Range("C4:G4")) - ws.Range("I5")).Resize(1, Range("I5")))
ws.Range("J5") = Application.Sum(Range("C4").Offset(0, Application.Count(Range("C4:G4")) - ws.Range("I5")).Resize(1, Range("I5")))
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
First cell in range: If using the exact same VBA code, the first cell in a range needs to be ("C4").
Last n values: In this example we are referencing to a cell ("I5") that contains a number of the last n values to sum. Therefore, if using the exact same VBA code you will need to capture a number of the last n values to sum in cell ("I5").
Row Range: In this example the values that we are summing are captured in range ("C4:G4"). Therefore, if using the exact same VBA code you will need to capture the values that you want to sum in range ("C4:G4").
Worksheet Name: Have a worksheet named Analysis.
First cell in range: If using the exact same VBA code, the first cell in a range needs to be ("C4").
Last n values: In this example we are referencing to a cell ("I5") that contains a number of the last n values to sum. Therefore, if using the exact same VBA code you will need to capture a number of the last n values to sum in cell ("I5").
Row Range: In this example the values that we are summing are captured in range ("C4:G4"). Therefore, if using the exact same VBA code you will need to capture the values that you want to sum in range ("C4:G4").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("J5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
First cell in range: Select the first cell in a range (row) from which to sum the last n values by changing the cell reference ("C4") in the VBA code to any cell that doesn't conflict with the formula.
Last n values: Select the last number of values that you want to sum in the specified row by changing the cell reference ("I5") in the VBA code to any cell that contains the number and doesn't conflict with the formula. You can also enter the number directly in the VBA code, by replacing 'Range("I5")' with a number.
Column Range: Select the column range where the values are captured that you want to sum by changing the range reference ("C4:G4").
Output Range: Select the output range by changing the cell references ("J5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
First cell in range: Select the first cell in a range (row) from which to sum the last n values by changing the cell reference ("C4") in the VBA code to any cell that doesn't conflict with the formula.
Last n values: Select the last number of values that you want to sum in the specified row by changing the cell reference ("I5") in the VBA code to any cell that contains the number and doesn't conflict with the formula. You can also enter the number directly in the VBA code, by replacing 'Range("I5")' with a number.
Column Range: Select the column range where the values are captured that you want to sum by changing the range reference ("C4:G4").
EXPLANATION
This tutorial shows and explains how to sum last n values in a row, by using an Excel formula or VBA.
This tutorial shows and explains how to sum last n values in a row, by using an Excel formula or VBA.
Excel Method: This tutorial provides a single Excel method that can be applied to sum last n values in a row, using a combination of Excel SUM, OFFSET and COUNT functions.
VBA Methods: This tutorial provides a single VBA method that last n values in a row by applying the SUM, OFFSET and COUNT functions in the VBA code.
FORMULA
SUM(OFFSET(first_cell,0,COUNT(range)-1,,-last_n_values))
SUM(OFFSET(first_cell,0,COUNT(range)-1,,-last_n_values))
ARGUMENTS
first_cell: The first cell in a specific row from which to begin summing the last n values.
last_n_values: A number that represents the last n values to be summed.
range: A range of cells (in a single row) that captures the values from which you want to sum the last n values.
first_cell: The first cell in a specific row from which to begin summing the last n values.
last_n_values: A number that represents the last n values to be summed.
range: A range of cells (in a single row) that captures the values from which you want to sum the last n values.
ADDITIONAL NOTES
Note 1: If a blank cell or a non-numeric value is captured in the last n values to be summed, the formula treats these the same as 0.
Note 1: If a blank cell or a non-numeric value is captured in the last n values to be summed, the formula treats these the same as 0.
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 methods | |
| Sum smallest n numbers | How to sum the smallest n numbers in a range using Excel and VBA methods | |
| Sum last n values in a column | How to sum the last n values in a column using Excel and VBA methods | |
| Sum first n values in a column | How to sum the first n values in a column using Excel and VBA methods | |
| Sum first n values in a row | How to sum the first n values in a row using Excel and VBA methods |
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 | |
| COUNT Function | The Excel COUNT function returns the number of cells that contain numeric values in a specified range |