Running Total of Column

How can I get the 'Total Hours' at the bottom of Column "B" to move down a row
each time I add another 'Employee Name' & 'Hours' in the next row. I also want
the 'Total Hours' to update with the added hours.
I currently have the formula: =Subtotal(9,B3:B9) in cell "B10".
Any help will be welcome. This is an example for the worksheet.
A B
Employee Name Hours
Fredrick Beauregard 8.00
Katie Mae Jones 2.00
Theodore Raymond 5.00
Samantha Jeans 4.00
Mike Marshall 2.00
Rick Danger 4.00

25.00

Thanks,
Bill E-mail to:

"WBromberek" wrote in message
...
How can I get the 'Total Hours' at the bottom of Column "B" to move
down a row
each time I add another 'Employee Name' & 'Hours' in the next row.
I also want
the 'Total Hours' to update with the added hours.
I currently have the formula: =Subtotal(9,B3:B9) in cell "B10".
Any help will be welcome. This is an example for the worksheet.
A B
Employee Name Hours
Fredrick Beauregard 8.00
Katie Mae Jones 2.00
Theodore Raymond 5.00
Samantha Jeans 4.00
Mike Marshall 2.00
Rick Danger 4.00

25.00

Thanks,
Bill E-mail to:




Hi Bill,

If you have labels and data in, say, B3:B9 as you outlined, and you want to
sum B3:B9, then enter the following in B10:

=SUM(B$3:B9)

Now, if you insert a new row between the existing B3 and B4 (say), the
formula will shift down to B11, and will now read:

=SUM(B$3:B10)

That should keep your sum working for you by fixing the top row at 3.

Note however that if you insert a new row below the last entry, immediately
above the SUM, it may not work since this is outside of the existing range.

Two ways around that:

1) If you enter your sum formula to start with in row 11 (not 10), and make
the formula include the empty row 10:

=SUM(B$3:B10)

Then if someone inserts a row below 9 and above 10 you should be okay.
However, if the user just 'uses' the empty row (10) you will be back where
you started above.

2) Use OFFSET to get your range:

Enter the following in B10:

=SUM(OFFSET(B10,-(ROW(B10)-3),0,ROW(B10)-3-1,1))

You may need to change the '3' to get it to pick up from the correct
starting point depending on your layout - hopefully you can see how it works
and generalise to adjust for your specific situation.

That formula will always work I think.


HTH,

Alan.

Thanks Alan
That keeps a running total =Subvtotal(9,B$2:B11) but how can I get the
formula cell "B12" to move down a row withoput inserting a new row each time. I
have an empty row between the last row with entries & total formula. Any ideas?
Thanks,
Bill

"WBromberek" wrote in message
...
Thanks Alan
That keeps a running total =Subvtotal(9,B$2:B11) but how can I get the
formula cell "B12" to move down a row withoput inserting a new row each
time. I
have an empty row between the last row with entries & total formula. Any
ideas?
Thanks,
Bill

Hi Bill,

I don't think I understand.

The running total formula cell will only need to move down *if* you insert
another row of data above - is that correct?

If not, how and why would you want the formula to move from, say, B12 to
B13?

Sorry if I am being slow!

Alan.