How to Change the SUM of a Range Dynamically in MS Excel

To change the SUM of a Range Dynamically is easier than you thought in MS Excel.

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?

SOLUTION:

Easy.
STEP 1) NAME the range dynamically by using the OFFSET function.
STEP 2) Use that NAME in your SUM formula.
——————————————————-

STEP 1:

1. Select the GROSS column.
2. Click Ctrl+F3 to display the NAME MANAGER.
3. Click New to create a new name.
4. 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.
5. Click Close.

STEP 2:

Define your SUM GROSS (cell E2) by entering the following formula in cell E2:

=SUM(GROSS)

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.