Creating dynamic Sum range if total is at the top of range

[bearcub]

Years ago I learned of an excel hack where you could replace the last range reference in a range with a cell refence using the name manager. The sum formula would have to be below the table like this:

The formula, as you can see is in AG 75. I replace the AG 74 will the RA which references the row above the $710,248.89.

Within the help of the name manager and RA range, if I wanted to include new values after row 73 I would need to manually change the cell range from AG 73 to AG74.

Is there a way to create an expanding range for formulas that would be added to the top of table. Meaning, if I inserted a row at the top of the table so the formula was in AG1, the RA formula would not work because I would not be referencing the cell before the formula. I looked at using the Index formula as the second range parameter but that includes a fixed range so it would not excpand.

Could I use the CountA function to replace the cell reference: Sum(Ag2: AG73) to Sum (Ag2: Counta(Ag2:Ag1000))

 

[Alex Blakenburg]

Why don't you just use an Excel Table ?

 

[Peter_SSs]

Or if you are going to put the SUM formula above the table, why not just make the formula =SUM(AG2:AG10000) or some other number that will easily allow for any amount of data you are likely to ever enter?