Hi Mal

Either
"=SUM(R6C4:R[-1]C4)"
for distinct D column, or
"=SUM(R6C:R[-1]C)"
for "this column".

HTH. Best wishes Harald

skrev i melding
...
Hi all;

Hopefully a simple problem...

In Excel 97 I want to sum integer fields in a column, where the 'sum'
field is the last item in the column. Say, 22 integers in column D,
and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting
rows above the cell containing the =SUM() entry does NOT always
adjust the range in the SUM parentheses correctly. Depends how you
insert rows, and where the inserting point is, and possibly on other
factors. Net result is that I can insert rows in the sheet which do
not get included correctly in the column sum. I've seen this inject
serious defects into many spreadsheets. It bit me again today.

I need to generate a formula which says "sum column D, starting at
cell $D6 and going through to (the cell above the sum cell)".

Excel Help says I can do this using RC[-1] notation, but the SUM
function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula
you typed contains an error". Web search didn't help. Lots of
tutorials and examples, nothing on this.

Can anyone help please ?

Thanks,

Mal.