How to make R1C1 nomenclature work with SUM() in a cell ?

[mal]

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.

 

[Tom Ogilvy]

=SUM(INDIRECT("D6"):R[-1]C)

activeCell.FormulaR1C1 = "=SUM(INDIRECT(""D6""):R[-1]C)"

 

[Jim Rech]

Also:

=SUM(D6:OFFSET(D28,-1,0))

--
Jim Rech
Excel MVP
| 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.
|

 

[Guest]

Hi
try in D28:
=SUM(OFFSET($D$28,-1,0,-ROW()+1))

 

[Harald Staff]

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

 

[Harald Staff]

Sorry. Didn't read the question carefully enough.
Best wishes Harald

 

[Guest]

Hi
then you use my formula:
=SUM(OFFSET($D$28,-1,0,-ROW()+1))

in cell D28 it should work. Maybe in your specific case use (as you want to
start with D6):
=SUM(OFFSET($D$28,-1,0,-ROW()+6))

Now if you insert rows BEFORE row 28 the formula would adapt automatically
(changing $D$28 to the new location)
Just try it

Hi all;

Hopefully a simple problem...

Can anyone help please ?

Many thanks for all the helpful suggestions- all very close to what I
want to achieve. Unfortunately, no luck yet.

Tom; you suggested:
=SUM(INDIRECT("D6"):R[-1]C)
....This gives "The formula you typed contains an error" with the
R[-1]C section highlighted.

Jim; you suggested
=SUM(D6:OFFSET(D28,-1,0))
....no errors, but requires me to know the cell D28 address, which is
what I'm trying to avoid (sorry if I was unclear). When rows are
inserted, the sum cell becomes D29, D30, D31... etc. Its address
isn't fixed. I tried using ROW() and COLUMN() but then I got "The
formula you typed contains an error"...

Mysterious Benefactor #3 you suggested...
=SUM(OFFSET($D$28,-1,0,-ROW()+1))
.... and this gives #REF! and also requires me to know the address of
D28...

Harald; you suggested
"=SUM(R6C4:R[-1]C4)" for distinct D column, or
"=SUM(R6C:R[-1]C)" for "this column"
....which is almost EXACTLY what I want to do.
....but both of these give me "The formula you typed contains an error"
with the RHS of the colon highlighted.


I've also tried =SUM(ADDRESS(etc) without luck. What on earth am I
doing wrong ?

Mal.

 

[mal]

Hi all;

Hopefully a simple problem...

Can anyone help please ?

Many thanks for all the helpful suggestions- all very close to what I
want to achieve. Unfortunately, no luck yet.

Tom; you suggested:
=SUM(INDIRECT("D6"):R[-1]C)
....This gives "The formula you typed contains an error" with the
R[-1]C section highlighted.

Jim; you suggested
=SUM(D6:OFFSET(D28,-1,0))
....no errors, but requires me to know the cell D28 address, which is
what I'm trying to avoid (sorry if I was unclear). When rows are
inserted, the sum cell becomes D29, D30, D31... etc. Its address
isn't fixed. I tried using ROW() and COLUMN() but then I got "The
formula you typed contains an error"...

Mysterious Benefactor #3 you suggested...
=SUM(OFFSET($D$28,-1,0,-ROW()+1))
.... and this gives #REF! and also requires me to know the address of
D28...

Harald; you suggested
"=SUM(R6C4:R[-1]C4)" for distinct D column, or
"=SUM(R6C:R[-1]C)" for "this column"
....which is almost EXACTLY what I want to do.
....but both of these give me "The formula you typed contains an error"
with the RHS of the colon highlighted.


I've also tried =SUM(ADDRESS(etc) without luck. What on earth am I
doing wrong ?

Mal.

 

[Jim Rech]

Jim; you suggested =SUM(D6:OFFSET(D28,-1,0))
....no errors, but requires me to know the cell D28 address

Mal-

D28 (in this example) is the cell you're entering the formula it. Surely
you know that<g>. And it will adjust automatically when you copy the
formula or insert rows.

--
Jim Rech
Excel MVP
| On Thu, 16 Dec 2004 12:08:03 GMT, (e-mail address removed) wrote:
|
| >Hi all;
| >
| >Hopefully a simple problem...
| >
| >Can anyone help please ?
| >
|
| Many thanks for all the helpful suggestions- all very close to what I
| want to achieve. Unfortunately, no luck yet.
|
| Tom; you suggested:
| =SUM(INDIRECT("D6"):R[-1]C)
| ...This gives "The formula you typed contains an error" with the
| R[-1]C section highlighted.
|
| Jim; you suggested
| =SUM(D6:OFFSET(D28,-1,0))
| ...no errors, but requires me to know the cell D28 address, which is
| what I'm trying to avoid (sorry if I was unclear). When rows are
| inserted, the sum cell becomes D29, D30, D31... etc. Its address
| isn't fixed. I tried using ROW() and COLUMN() but then I got "The
| formula you typed contains an error"...
|
| Mysterious Benefactor #3 you suggested...
| =SUM(OFFSET($D$28,-1,0,-ROW()+1))
| ... and this gives #REF! and also requires me to know the address of
| D28...
|
| Harald; you suggested
| "=SUM(R6C4:R[-1]C4)" for distinct D column, or
| "=SUM(R6C:R[-1]C)" for "this column"
| ...which is almost EXACTLY what I want to do.
| ...but both of these give me "The formula you typed contains an error"
| with the RHS of the colon highlighted.
|
|
| I've also tried =SUM(ADDRESS(etc) without luck. What on earth am I
| doing wrong ?
|
| Mal.

 

[Tom Ogilvy]

Copied from the Excel Formula Bar - worked fine for me if I am using R1C1
addressing.

You can't type a formula in R1C1 format if you are in A1 mode. (and you get
the error you describe).

--
Regards,
Tom Ogilvy

Hi all;

Hopefully a simple problem...

Can anyone help please ?

Many thanks for all the helpful suggestions- all very close to what I
want to achieve. Unfortunately, no luck yet.

Tom; you suggested:
=SUM(INDIRECT("D6"):R[-1]C)
...This gives "The formula you typed contains an error" with the
R[-1]C section highlighted.

Jim; you suggested
=SUM(D6:OFFSET(D28,-1,0))
...no errors, but requires me to know the cell D28 address, which is
what I'm trying to avoid (sorry if I was unclear). When rows are
inserted, the sum cell becomes D29, D30, D31... etc. Its address
isn't fixed. I tried using ROW() and COLUMN() but then I got "The
formula you typed contains an error"...

Mysterious Benefactor #3 you suggested...
=SUM(OFFSET($D$28,-1,0,-ROW()+1))
... and this gives #REF! and also requires me to know the address of
D28...

Harald; you suggested
"=SUM(R6C4:R[-1]C4)" for distinct D column, or
"=SUM(R6C:R[-1]C)" for "this column"
...which is almost EXACTLY what I want to do.
...but both of these give me "The formula you typed contains an error"
with the RHS of the colon highlighted.


I've also tried =SUM(ADDRESS(etc) without luck. What on earth am I
doing wrong ?

Mal.