Referring to "current cell row minus one" in a SUM function

Posted by Fred P on August 16, 2001 12:28 AM

I need to be able to sum a column of values, e.g.,
A11 SUM(A1:A10), however if someone manually inserts
a row at row 11, I want the sum function to take this
new row into account, as in
A12 SUM(A1:A11)

if I could treat the cell reference as a variable:

Axx SUM(A1:Acurrent_row - 1)

I'd rather not use VBA for something this simple.

this would solve my problem, can this be done?

Posted by anno on August 16, 2001 12:49 AM

fred
i think a dynamic range will give you what you need. go here to see how it's done:
14626b.html

Posted by Aladin Akyurek on August 16, 2001 1:16 AM

A named dynamic range would "require" that you do the summing NOT in a cell of a column where the numbers to sum are.

Aladin

Posted by anno on August 16, 2001 1:37 AM

Aladin - I didn't know that - thanks for clarifying

Posted by Scott R on August 16, 2001 11:41 AM

In instances like yours I create a relative range, "CellUp", that's defined as 1 cell above the current cell. Assuming your active cell is A11 put =A10 (no $ signs) in the "Refers to:" of the Define Name box. Then use SUM(A1:CellUp) in A11. I need to be able to sum a column of values, e.g.,

Posted by Fred P on August 16, 2001 8:19 PM

Re: Aladin - I didn't know that - thanks for clarifying

Thanks to all that replied.

I was hoping for something rather simple like:

=SUM(A1:ADDRESS(ROW()-1,COLUMN())

which doesn't work, presumably due to lack of
'text' to 'reference' type conversion (How do you do that?)

Named ranges don't appear to dynamically update
after a row is inserted through it until after a file save as in SUM(Prices) where 'Prices' is the
column header text

Posted by Aladin Akyurek on August 16, 2001 11:19 PM

Named dynamic ranges

Fred, I was hoping for something rather simple like: =SUM(A1:ADDRESS(ROW()-1,COLUMN()) which doesn't work, presumably due to lack of

That would be:

=SUM(A1:INDIRECT(ADDRESS(ROW()-1,1)))

=SUM(INDIRECT("A1:"&ADDRESS(ROW()-1,1)))

What Scott R gave you is much better, if you insist on having the SUM formula immediately after your last entry. Named ranges don't appear to dynamically update

You're right about "named ranges". Anno wanted you to use something different: a named *dynamic* range.

Activate Insert|Name|Define.
Enter Prices for Names in Workbook [ if this name is already there, keep it. ]
Enter (or Change what is already there to) for Refers To:

=OFFSET(x!$A$1,0,0,COUNTA($A:$A),1)

x stands for the name of the worksheet you're in.

Put =SUM(Prices) anywhere except where prices are.
Just watch it while you insert values or rows with values in or delete values or rows with values from anywhere in column A.

Aladin

Posted by Fred P on August 19, 2001 7:35 PM

Re: Named dynamic ranges

Thanks very much Scott R and Aladin, this works ok.

Fred.