Excel SUM and OFFSET Formula

Use SUM and OFFSET to find totals for dynamic ranges of data

If your Excel worksheet includes calculations based on a changing range of cells, using the SUM and OFFSET functions together in a SUM OFFSET formula simplifies the task of keeping the calculations up to date. 

Create a Dynamic Range With the SUM and OFFSET Functions

Creating a Dynamic Range with the SUM-OFFSET Formula
© Ted French

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 occurs when the data is inserted into the cell where the function is currently located.

In the example image that accompanies this article, the new sales figures for each day are added to the bottom of the list, which forces the total to continually shift down one cell each time the new data is added.

If the SUM function were 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. 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.

Syntax and Arguments

Refer to the image that accompanies this article to follow along with this tutorial.

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 endpoint to the range of data totaled by the formula. This is accomplished by setting the endpoint of the range to one cell above the location of the formula.

The formula's syntax:

=SUM(Range Start:OFFSET(Reference,Rows,Cols))

Range Start – (required) the starting point for the range of cells that will be totaled by the SUM function. In the example image, this is cell B2.

Reference – (required) the cell reference used for calculating the range's endpoint located many rows and columns away. In the example image, the Reference argument is the cell reference for the formula itself since we always want the range to 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.

Using the SUM OFFSET Formula to Total Sales Data

This example uses a SUM OFFSET formula to return the total for the daily sales figures listed in column B of the worksheet.

Initially, the formula was entered into cell B6 and totaled the sales data for four days.

The next step is to move the SUM OFFSET formula down a row to make room for the fifth day's sales total.

This is accomplished by inserting a new row 6, which moves the formula down to row 7.

As a result of the move, Excel automatically updates the Reference argument to cell B7 and adds cell B6 to the range summed by the formula.

Entering the SUM OFFSET Formula

  1. Click on cell B6, which is the location where the results of the formula will initially be displayed.
  2. Click on the Formulas tab of the ribbon menu.
  3. Choose Math & Trig from the ribbon to open the function drop-down list.
  4. Click on SUM in the list to bring up the function's dialog box.
  5. In the dialog box, click on the Number1 line.
  6. Click on cell B2 to enter this cell reference into the dialog box. This location is the static endpoint for the formula;
  7. In the dialog box, click on the Number2 line.
  8. Enter the following OFFSET function: OFFSET(B6,-1,0) to form the dynamic endpoint for the formula.
  9. Click OK to complete the function and close the dialog box.

The total $5679.15 appears in cell B7.

When you click on cell B3, the complete function =SUM(B2:OFFSET(B6,-1,0)) appears in the formula bar above the worksheet.

Adding the Next Day's Sales Data

To add the next day's sales data:

  1. Right-click on the row header for row 6 to open the context menu.
  2. In the menu, click on Insert to insert a new row into the worksheet.
  3. As a result, the SUM OFFSET formula moves down to cell B7 and row 6 is now empty.
  4. Click on cell A6.
  5. Enter the number 5 to indicate that the sales total for the fifth day is being entered.
  6. Click on cell B6.
  7. Type the number $1458.25 and press the Enter key on the keyboard.

Cell B7 updates to the new total of $7137.40.

When you click on cell B7, the updated formula =SUM(B2:OFFSET(B7,-1,0)) appears in the formula bar.

Note: The OFFSET function has two optional arguments: Height and Width, which were 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.

Was this page helpful?