By itself, theSUM functioncan usually accommodate the insertion of new cells of data into the range being summed.
One exception comes up whenever the data is inserted into the cell where the function is currently located.
To follow along with this tutorial, open a blank Excel worksheet and enter the sample data.
This is accomplished by setting the endpoint of the range to one cell above the location of the formula.
Initially, the formula was entered into cell B6 and totaled the sales data for four days.
This is accomplished byinserting a new row6, which moves the formula to row 7.
Select cellB6, which is the location where the formula results will initially display.
Select theFormulastab of theribbon.
In theFunction Argumentsdialog box, place the cursor in theNumber1text box.
In the worksheet, select cellB2to enter this cell reference in the dialog box.
This location is the static endpoint for the formula.
In theFunction Argumentsdialog box, place the cursor in theNumber2text box.
EnterOFFSET(B6,-1,0).
This OFFSET function forms the dynamic endpoint for the formula.
SelectOKto complete the function and terminate the dialog box.
The total appears in cell B6.
SelectInsertto 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 cellA6and enter the number5to indicate that the sales total for the fifth day is being entered.
Select cellB6, enter$1458.25, then pressEnter.
Cell B7 updates to the new total of $7137.40.
When you select cell B7, the updated formula appears in the formula bar.
The OFFSET function has two optional arguments: Height and Width,which were not used in this example.
