© Ugur Akinci
Let’s say you have a data table like this:
PROBLEM: We want to create a SUM for the GROSS column that would adjust itself dynamically when new sales records are added to the table.
How would you do that?
STEP 1) NAME the range dynamically by using the OFFSET function.
STEP 2) Use that NAME in your SUM formula.
- Select the GROSS column.
- Click Ctrl+F3 to display the NAME MANAGER.
- Click New to create a new name.
- Enter the following formula for the new range name GROSS:= OFFSET(Sheet1!$B$1,1,0,COUNTA($B:$B)) NOTE: If you are not using “Sheet1″ but another worksheet, please use the appropriate worksheet number or name in the formula.
- Click Close.
Define your SUM GROSS (cell E2) by entering the following formula in cell E2:
The sum of all GROSS sales is calculated automatically:
TEST: Now let’s insert two new records and see if the SUM GROSS is updated dynamically (automatically):
RESULT: Yes, the SUM GROSS is adjusted automatically.