Adding a new row to a worksheet and having the sum formula automaticly update???Help

M

MatthewB.

I am not 100% sure how to explain this, so here is my best. I have a worksheet that contains dates(months) and numbers
A
Dec-01 19
Jan-02 10
Feb-02 15
March-02 20
April-02 14
May-02 5
Last 5months =sum(B2:B6
Total =sum(B1:B6
My problem is that i want to imput a new row...June-02 I want the last 5 months to look at the info in B3-B7. Is there any formula that will automaticly do this everytime I add a new row/month

Also, Is there any way to have a sum funtion automaticly add the new row into the total function

Please help!!!!!!!!!!!!!
 
D

DDM

Matthew, in B7, this will total the last five months:
=SUBTOTAL(9,(OFFSET(B7,-5,0,5)));
and in B8, this will total the column: =SUBTOTAL(9,B1:B7). Note that the
range is B1:B7. The SUBTOTAL function ignores the formula in B7.

Both will automatically update when you insert rows immediately above.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

MatthewB. said:
I am not 100% sure how to explain this, so here is my best. I have a
worksheet that contains dates(months) and numbers:
A B
Dec-01 190
Jan-02 100
Feb-02 150
March-02 200
April-02 140
May-02 50
Last 5months =sum(B2:B6)
Total =sum(B1:B6)
My problem is that i want to imput a new row...June-02 I want the last 5
months to look at the info in B3-B7. Is there any formula that will
automaticly do this everytime I add a new row/month.
 
M

MatthewB

Thanks DDM

The first funtion worked fine, but the 2nd one is not adding in the added rows

But the 1st part was the most important. Thank a million.
 
D

DDM

Matthew, what I would suggest is that when adding rows, you select (in your
example) A6:B6, right-click them, and Insert > Shift cells down > OK. That
should compel the second formula to include the inserted row.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top