Sum Column above current cell dynamically using function call

[TopSlice]

I want to sum the column above current cell using the formula

=SUM(C3:activecell's row - 1)

My question is how do I determine the row number above the current cell.
I know CELL("row") returns the current row but could not find a way of
using this in the formula.

I know there are ways round this eg. SUM(C3:Cn) where n is manually entered
but I want to know of a way to do it using an Excel function.

 

[vezerid]

Maybe something like

=SUM(C3:INDIRECT(ADDRESS(ROW(),3)))

HTH
Kostis Vezerides

 

[Glenn]

I want to sum the column above current cell using the formula

=SUM(C3:activecell's row - 1)

My question is how do I determine the row number above the current cell.
I know CELL("row") returns the current row but could not find a way of
using this in the formula.

I know there are ways round this eg. SUM(C3:Cn) where n is manually entered
but I want to know of a way to do it using an Excel function.

Try this:

=IF(CELL("row")>3,SUM(INDIRECT("C3:C"&CELL("row")-1)),"")

 

[Mike H]

Hi,

Try this

=SUM(INDIRECT("C3:C"&CELL("Row")-1))

Mike

 

[Dave Peterson]

Another one:
=SUM(INDIRECT("r3c3:r[-1]c",FALSE))

R3C3 is row3, column 3
r[-1]c is row of the cell with the formula - 1, same column as the cell with
the formula

And another one:
=SUM(C3:OFFSET(C11,-1,0))
(where c11 is the cell getting the formula)


But why not just use the address of the cell that's above the cell with the
formula.

 

[Ashish Mathur]

Hi,

Why not simply =sum(C3:C7). This assumes that your current cell is C8

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[pshepard]

Hi Topslice,

=sum(c3:indirect("c:"&row()-1))

 

[TopSlice]

Thanks guys - its been a great help.
My findings from your suggestions are:


=SUM(INDIRECT("r3c3:r[-1]c",FALSE)) works


=SUM(INDIRECT("C3:C"&CELL("Row")-1)) works but if you insert a row the
value changes -
how do you make it automatically
recalc the sum?


=SUM(C3:OFFSET(C11,-1,0)) works but not if need to update C3 if insert a
row above R3 or delete row 3


=sum(c3:indirect("c:"&row()-1)) does not work


Changed =SUM(C3:INDIRECT(ADDRESS(ROW(),3) ) ) =>

=SUM(D4:INDIRECT(ADDRESS(ROW()-1,COLUMN(),4))) which works

Thanks Again
TopSlice