SUM OFFSET Formula Overview
For example, if you use calculations for a period of time that continually changes - such as totaling sales for the month - the OFFSET function allows you to set up a dynamic range that keeps changing as each day's sales figures are added.
By itself, the SUM function can usually accommodate new cells of data being inserted into the range being summed.
One exception, however, is when the data is inserted into the cell where the function is currently located.
In the image above, the new sales figures for each day are added to the bottom of the list - which forces the total to continual shift down one cell each time the new data is added.
If the SUM function was used on its own to total the data, it would be necessary to modify the range of cells used as the function's argument each time new data was added.
By using the SUM and OFFSET functions together, however, the range that is totaled becomes dynamic. Or, in other words, it changes to accommodate new cells of data. The addition of new cells of data does not cause problems because the range continues to adjust as each new cell is added.
SUM OFFSET Formula Breakdown
In this formula, the SUM function is used to total the range of data supplied as its argument. The start point for this range is static and is identified as the cell reference to the first number to be totaled by the formula.
The OFFSET function is nested inside the SUM function and is used to create a dynamic end point to the range of data totaled by the formula. This is accomplished by setting the end point of the range to one cell above the location of the formula.
The formula's syntax would be:
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
Range Start - (required) the starting point for range of cells that will be totaled by the SUM function. In the example in the image above, this will be cell E2
Reference - (required) the cell reference used for calculating the range's end point located so many rows and columns away. In the example in the image above, the Reference argument is the cell reference for the formula itself since we always want the range end one cell above the formula
Rows - (required) the number of rows above or below the Reference argument used in calculating the offset. This value can be positive, negative, or set to zero
If the offset's location is above the Reference argument this value is negative. If it is below, the Rows argument is positive. If the offset is located in the same row, this argument is zero. In this example the offset begins one row above the Reference argument so the value for this argument is negative one (-1).
Cols - (required) the number of columns to the left or right of the Reference argument used in calculating the offset. This value can be positive, negative, or set to zero
If the offset's location is to the left of the Reference argument this value is negative. If to the right, the Cols argument is positive. In this example the data being totaled is in the same column as the formula so the value for this argument is zero.
Example Using the SUM - OFFSET Formula to Total Sales Data
As can be seen in the image above, this example uses a SUM OFFSET formula to return the total for the daily sales figures listed in column E of the worksheet.
Initially, the formula will be entered into cell E6 and total the sales data for four days.
The next step in the example will be to move the SUM OFFSET formula down a row in order to make room for the fifth day's sales total.
This will be accomplished by inserting a new row 6, which will move the formula down to row 7.
As a result of the move, Excel will automatically update the Reference argument to cell E7 and add cell E6 to the range summed by the formula.
Entering the Tutorial Data
- Enter the following data into cells D1 to F3
Cell Data D1 - Day D2 - 1 D3 - 2 D4 - 3 D5 - 4 D6 - Total: E1 - Sales Total E2 - $1,325.47 E3 - $1,502.69 E4 - $1,452.14 E5 - $1,398.85
Entering the SUM OFFSET Formula
- Click on cell E6 - the location where the results of the formula will initially be displayed
- Click on the Formulas tab of the ribbon menu
- Choose Math & Trig from the ribbon to open the function drop down list
- Click on SUM in the list to bring up the function's dialog box
- In the dialog box, click on the Number1 line
- Click on cell E2 to enter this cell reference into the dialog box - this location is the static end point for the formula
- In the dialog box, click on the Number2 line
- Enter the following OFFSET function: OFFSET(E6,-1,0) - this forms the dynamic end point for the formula
- Click OK to complete the function and close the dialog box
- The total $5679.15 should appear in cell E6
- When you click on cell E3 the complete function =SUM(E2:OFFSET(E6,-1,0)) appears in the formula bar above the worksheet
Adding the Next Day's Sales Data
- Right Click on the row header for row 6 to open the context menu
- In the menu, click on Insert to insert a new row into the worksheet
- As a result, the SUM - OFFSET formula moves down to cell E7 and row 6 is now empty
- Click on cell D6
- Enter the number 5 - to indicate that the sales total for the fifth day is being entered
- Click on cell E6
- Type the number $1458.25 and press the Enter key on the keyboard
- Cell E7 should update to the new total of $7137.40
- When you click on cell E7 the updated formula =SUM(E2:OFFSET(E7,-1,0)) appears in the formula bar
Note: The OFFSET has two optional arguments - Height and Width which have been omitted in this example.
These arguments can be used to tell the OFFSET function the shape of the output in terms of it being so many rows high and so many columns wide.
By omitting these arguments, the function, by default, uses the height and width of the Reference argument instead, which, in this example is one row high and one column wide.