Summing a dynamic range

[mojitoman6]

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.

 

[Smitty]

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,

 

[HalfAce]

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?

 

[mojitoman6]

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?

 

[L. Howard]

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

 

[Smitty]

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!