Excel Sum And Offset Formula

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

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 — use the OFFSET function in Excel to set up a dynamic range that changes as each day’s sales figures are added.

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

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 below, 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 only the SUM function is used to total the data, the range of cells used as the function argument would need to be modified each time new data is added.

To follow along with this tutorial, open a blank Excel worksheet and enter the sample data. Your worksheet doesn’t need to be formatted like the example, but be sure to enter the data in the same cells.

By using the SUM and OFFSET functions together, the range that is totaled becomes dynamic and 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

In this formula, the SUM function is used to total the range of data supplied as the 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 creates 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 syntax is:

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

The arguments are:

• Range Start: The starting point for the range of cells that will be totaled by the SUM function. In this example, the starting point is cell B2.Reference: The required cell reference used to calculate the range endpoint. In the example, the Reference argument is the cell reference for the formula because the range ends 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 location is above the Reference argument, the value is negative. If the offset 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 the argument is negative one (-1).Cols: The number of columns to the left or right of the Reference argument used to calculate the offset. This value can be positive, negative, or set to zero. If the offset location is to the left of the Reference argument, this value is negative. If the offset is to 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.

Use 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 one row to make room for the fifth day’s sales total. This is accomplished by inserting a new row 6, which moves the formula 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.

• Select cell B6, which is the location where the formula results will initially display.
• Select the Formulas tab of the ribbon.
• Choose Math & Trig.
• Select SUM.
• In the Function Arguments dialog box, place the cursor in the Number1 text box.
• In the worksheet, select cell B2 to enter this cell reference in the dialog box. This location is the static endpoint for the formula.
• In the Function Arguments dialog box, place the cursor in the Number2 text box.
• Enter OFFSET(B6,-1,0). This OFFSET function forms the dynamic endpoint for the formula.
• Select OK to complete the function and close the dialog box. The total appears in cell B6.

Add the Next Day’s Sales Data

To add the next day’s sales data:

Select cell B6, which is the location where the formula results will initially display.

Select the Formulas tab of the ribbon.

Choose Math & Trig.

Select SUM.

In the Function Arguments dialog box, place the cursor in the Number1 text box.

In the worksheet, select cell B2 to enter this cell reference in the dialog box. This location is the static endpoint for the formula.

In the Function Arguments dialog box, place the cursor in the Number2 text box.

Enter OFFSET(B6,-1,0). This OFFSET function forms the dynamic endpoint for the formula.

Select OK to complete the function and close the dialog box. The total appears in cell B6.

• Right-click the row header for row 6.
• Select Insert to insert a new row into the worksheet. The SUM OFFSET formula moves down one row to cell B7 and row 6 is now empty.
• Select cell A6 and enter the number 5 to indicate that the sales total for the fifth day is being entered.
• Select cell B6, enter $1458.25, then press Enter.
• Cell B7 updates to the new total of $7137.40.

When you select cell B7, the updated formula appears in the formula bar.

Right-click the row header for row 6.

Select Insert to insert a new row into the worksheet. The SUM OFFSET formula moves down one row to cell B7 and row 6 is now empty.

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

Select cell B6, enter $1458.25, then press Enter.

 Cell B7 updates to the new total of $7137.40.

=SUM(B2:OFFSET(B7,-1,0))

The OFFSET function has two optional arguments: Height and Width, which were not used in this example. These arguments tell the OFFSET function the shape of the output in terms of the number of rows and columns.

By omitting these arguments, the function uses the height and width of the Reference argument instead, which, in this example is one row high and one column wide.

Get the Latest Tech News Delivered Every Day