Excel SUM and OFFSET Formula

How to Find the Totals for Dynamic Ranges of Data

woman working with spreadsheet on laptop

Westend61 / Getty Images

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. 

Instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Office 365.

Create a Dynamic Range With the SUM and OFFSET Functions

If you use calculations for a period of time that continually changes — such as determining sales for the month — the OFFSET function in Excel allows you to set up a dynamic range that changes as each day's sales figures are added.

By itself, the SUM function can usually accommodate the insertion of new cells of data 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 above, the new sales figures for each day are added at the bottom of the list, forcing the total to continually shift down one cell each time as 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

Screenshot of example data in Excel

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: The starting point for the range of cells that will be totaled by the SUM function is required. In the example image, this is cell B2.
  • Reference: The required cell reference used for calculating the range's endpoint is located many rows and columns away. In the example image, the Reference argument is the cell reference for the formula itself because you always want the range to end one cell above the formula.
  • Rows: The number of rows above or below the Reference argument used in calculating the offset is required. This value can be positive, negative, or set to zero. If the offset's location is above the Reference argument, the value is negative. If it is below, the Rows argument is positive. If the offset is located in the same row, the 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: The number of columns to the left or right of the Reference argument used in calculating the offset is required. 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.

  1. Select cell B6, which is the location where the results of the formula will initially be displayed.

  2. Select the Formulas tab of the ribbon.

    Formula tab in Excel
  3. Choose Math & Trig from the ribbon to open the function drop-down list.

    Math & Trig button
  4. Select SUM in the list to bring up the function's dialog box.

    SUM menu item in Excel
  5. In the dialog box, select the Number1 line.

  6. Select cell B2 to enter this cell reference into the dialog box. This location is the static endpoint for the formula.

    Number1 field with B2 in it
  7. In the dialog box, select the Number2 line.

  8. Enter the following OFFSET function: OFFSET(B6,-1,0) to form the dynamic endpoint for the formula.

    Number2 field with OFFSET(B6,-1,0) in it
  9. Select OK to complete the function and close the dialog box. The total appears in cell B6.

    Screenshot of total and formula in B7

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. Select Insert to insert a new row into the worksheet. As a result, the SUM OFFSET formula moves down to cell B7 and row 6 is now empty.

    Insert command in Excel
  3. Select cell A6 and enter the number 5 to indicate that the sales total for the fifth day is being entered.

  4. Select cell B6, enter $1458.25 and press the Enter key on the keyboard.

    New total inserted in Excel
  5.  Cell B7 updates to the new total of $7137.40.

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

The OFFSET function has two optional arguments: Height and Width, which were not used in this example.

These arguments can be used to tell the OFFSET function the shape of the output in terms of 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.