ExcelBanter - View Single Post - How to make R1C1 nomenclature work with SUM() in a cell ?

[email protected]

On Thu, 16 Dec 2004 12:08:03 GMT, 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.