# Summing a dynamic range

#### mojitoman6

##### New Member
Hello, I would like to sum a range in excel that is always changing... The reason it is always changing is because there are rows constantly being added above the whole range on the top of the sheet (so the beginning and the end of the range are changing, nothing is constant).

The catch is I am also adding rows to the end of the sum range itself! I tried using the indirect function and this worked for adding rows to the end of the sum range, but it was thrown off when new rows were added in a different area of the sheet above the sum range.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
If you set the data up as a table you can use a structured reference to the column in question, which will be dynamic.

=SUM(Select the column in question, and Excel will build the reference)

HTH,

Can you give the current range of interest a defined name?
Then any future rows added above or within the defined range will be included in the range.

(Go to the Formulas tab on the ribbon, & find the Name Manager & Defined Name, give it a name and hit OK.)

Refer to the named range in your formula(s) and your range will be dynamic.

Hope it helps.

EDIT: Hey Smitty! how's the world treating you?

I like Smitty's answer because I already have a Macro that adds rows to a named range and then renames the range with the new row included...

Smitty, let me ask you an amateur question.. I have a date at the top of my column and the sum is adding the date into the calculation.. since when is a date a number?

Hi mojitoman6,

They have always been a number. In a cell formatted as Number, type in =Now().

It will be pretty close to this number 42163.87 (changing with every calculate event).

I believe it is a number expressing the number of days, hours, minutes and seconds from Jan 00 1904 to present.

Howard

Just like Howard said, Dates have always been numbers, so you need to exclude that row from the sum range.

Note that with the table approach, you don't need to worry about renaming your range, as the table range will automatically expand.

Heya Dan! Life would be great if I could get back home and not be in LA!

Replies
1
Views
260
Replies
4
Views
142
Replies
5
Views
275
Replies
1
Views
166
Replies
0
Views
600

1,196,290
Messages
6,014,504
Members
441,825
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back