Latest posts by techwriter (see all)
- How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel - October 19, 2016
- FREE Online Video Course – MS Word Power Shortcuts - October 14, 2016
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
© 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.