Remember Me?

Posted to microsoft.public.excel.newusers
 Robert Posts: n/a Sum all rows above

I want to put into a cell the total of all numbers above that cell in that
column. I want to do this without specifying specific rows. In this way,
if a row were added above the total, it would be included in the total
correctly.

Robert

 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: Sum all rows above

Hi Robert,

To sum all rows above a specific cell without specifying specific rows, you can use the SUM function with a cell range that starts from the first cell in the column and ends at the cell just above the cell where you want to display the total.

Here are the steps to do this:
1. Select the cell where you want to display the total.
2. Type the following formula:
Code:
`=SUM(A1:A"current row number"-1)`
, where "current row number" is the row number of the cell where you want to display the total.

For example, if you want to display the total in cell C5, the formula would be
Code:
`=SUM(C1:C4)`
.
3. Press Enter to calculate the total.

Now, the cell will display the total of all the numbers above it in that column. If you add a new row above the total, the formula will automatically update to include the new row in the total.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.newusers
 Ardus Petus Posts: n/a Sum all rows above

Hi Robert,

Try:
=SUM(OFFSET(A1,,,ROW()-1,1))

HTH
--
AP

"Robert" a écrit dans le message de news:
...
I want to put into a cell the total of all numbers above that cell in that
column. I want to do this without specifying specific rows. In this way,
if a row were added above the total, it would be included in the total
correctly.

Robert

Posted to microsoft.public.excel.newusers
 davesexcel Posts: n/a Sum all rows above

=sum(indirect("r1c"&column()&":r"& (row()-1)&"c"&column(),false))

--
davesexcel

------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708

Posted to microsoft.public.excel.newusers
 Ragdyer Posts: n/a Sum all rows above

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Robert" wrote in message
...
I want to put into a cell the total of all numbers above that cell in that
column. I want to do this without specifying specific rows. In this way,
if a row were added above the total, it would be included in the total
correctly.

Robert

Posted to microsoft.public.excel.newusers
 Dave Peterson Posts: n/a Sum all rows above

One more.

If your formula is in A101:
=sum(a1:offset(a101,-1,0))

Robert wrote:

I want to put into a cell the total of all numbers above that cell in that
column. I want to do this without specifying specific rows. In this way,
if a row were added above the total, it would be included in the total
correctly.

Robert

--

Dave Peterson
Posted to microsoft.public.excel.newusers
 Robert Posts: n/a Thanks to all who posted

"Robert" wrote in message
...
I want to put into a cell the total of all numbers above that cell in that
column. I want to do this without specifying specific rows. In this way,
if a row were added above the total, it would be included in the total
correctly.

Robert

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post seadragon69 Excel Worksheet Functions 2 December 7th 05 05:54 PM ausdiver99 Excel Worksheet Functions 1 June 2nd 05 02:15 PM KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM Mr. G. Excel Worksheet Functions 3 March 31st 05 03:49 AM marika1981 Excel Discussion (Misc queries) 1 February 18th 05 02:40 AM

All times are GMT +1. The time now is 04:30 PM. Copyright ©2004-2023 ExcelBanter.