Categories

Users Online

+ 13 Guests + 7 Bots

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

© Ugur Akinci

Let’s say you have a data table like this:

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

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:

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

TEST: Now let’s insert two new records and see if the SUM GROSS is updated dynamically (automatically):

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

RESULT: Yes, the SUM GROSS is adjusted automatically.

Share

Leave a Reply

  

  

  


*

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>