Where 9999 is any number larger than possible in your range
=SUM(A1:OFFSET(A1,MATCH(99999,A:A),0))

This is even better because it does not depend on the column ID:

=SUM(INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))

Ian wrote...

The other respondents were warm, but the best answer involving volatile
functions is

=SUM(INDIRECT("R2C7:R[-1]C7",0))

if you always want to refer to column G (the 7th column in the
worksheet). If the formula above would have been entered in column G in
the row immediately below the values to be summed, and if you'd be
doing this for other columns as well, try

=SUM(INDIRECT("R2C:R[-1]C",0))

But you don't need to use volatile functions.

=SUM(G$2:INDEX(G:G,ROW()-1))

If the MIN and MAX formulas would be underneath the SUM formula, they'd
need to refer, respectively, to ranges ending 2 or 3 rows above, so

=MIN(G$2:INDEX(G:G,ROW()-2))

and

=MAX(G$2:INDEX(G:G,ROW()-3))

Another one if the formula is going in G100:

=sum(g2:offset(g99,-1,0))

Adjust the 99 to be one less row than the cell's row that's getting the formula.

But I'm confused about what you mean about storing it in another worksheet.

And an alternative suggestion...

Insert a new Row 1 and put the formula in that.

=sum(g3:g65536)
(headers in row 2)

And if you use window|Freeze panes, your totals will always be visible.

And if you used data|filter|autofilter and =subtotal() as your formula, then
those formulas would just count/sum... the visible cells.