You can use the following syntax with the SUM and OFFSET functions in Excel to sum a specific range of values that are offset by a specific number of rows and columns:
=SUM(OFFSET(A1, 6, 2, 5, 1))
This particular formula will sum the range of values that is 5 rows long and 1 column wide that is offset by 6 rows below and 2 columns to the right of cell A1.
The following example shows how to use this formula in practice.
Example: How to Use SUM and OFFSET in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams and positions:
Notice that each team has exactly five players.
Suppose we would like to sum the values of the points scored by the players on the Spurs team.
We can type the following formula into cell E2 to do so:
=SUM(OFFSET(A1, 6, 2, 5, 1))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 92, which is the sum of the points values for players on the Spurs team.
We can confirm that this is correct by manually calculating the sum of points for the Spurs players:
Sum of Points for Spurs: 6 + 8 + 15 + 40 + 23 = 92.
This matches the value calculated by our formula.
How This Formula Works
Recall the formula that we used to calculate the sum of the points for players on the Spurs team:
=SUM(OFFSET(A1, 6, 2, 5, 1))
Here is a visual explanation of how the formula works:
First, we start at cell A1.
Then we go 6 cells below this cell and 2 cells to the right.
Then we specify that we’d like to look at the cell range that is 5 rows long and 1 column wide.
Lastly, we use the SUM function to sum the values in this range.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use INDEX and MATCH to Return Multiple Values
Excel: How to Use OFFSET and MATCH Together
Excel: How to Use VLOOKUP to Return All Matches
