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.

Maybe something like

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

HTH
Kostis Vezerides

On Sep 14, 6:51*pm, TopSlice
wrote:
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.

TopSlice wrote:
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)),"")

Hi,

Try this

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

Mike

"TopSlice" wrote:

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.

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.


TopSlice wrote:

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.

--

Dave Peterson

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"TopSlice" wrote in message
...
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.

Hi Topslice,

=sum(c3:indirect("c:"&row()-1))
--
If this post helps click Yes
---------------
Peggy Shepard


"Ashish Mathur" wrote:

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"TopSlice" wrote in message
...
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.

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


=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