Adding Rows to Formulas

[teq]

Greetings - I am new to this Forum and I'm pretty sure my question is a simple one, but it's driving me insane.

My worksheet has several columns with totals at the bottom. My problem is, whenever I add a row to the top, it is not included in my @sum column total. Is there a way I can set up my worksheet so any row added to the top are automatically included in the column total at the bottom?

Thank you for your help!

[duane]

I'd suggest leaving row 1 blank at all times, with your data starting on row 2. Make your sum form row 1 to the end of your data. them to add a new row, insert a new row 1 and place the data there (which will become row 2)

[teq]

I like your solution - very simple and practical. I sometimes have a space problem, but to avoid the errors I've been making, I need to make this change. Thank you!

[josnah]

Try this:

For Example:

Instead of
=SUM(A2:A5)

use
=SUM(OFFSET(A$1,1,0):A5)

This should work if you keep adding rows just after Row 1...

[teq]

Hi Josnah - thanks for the formula - I can't seem to get it to work for me - it won't add include A1 to the total. I will have to study up and see what the Offset command does. Thanks again!

[BigBas]

Hi Josnah - thanks for the formula - I can't seem to get it to work for me - it won't add include A1 to the total. I will have to study up and see what the Offset command does. Thanks again!

Using his formula, if you want to include A1, make this change:

=SUM(OFFSET($A$1,0,0):A5)

[josnah]

Hi Teq,

I'm assuming Row 1 is a Header Row and also that the data to be calculated is in Col A.
You would need to change that accordingly to suit your worksheet.
It works fine with me... So I'm not sure where its going wrong...
See the attached image...

If you need to add Row 1 to the sum then you have to amend the formula as follows:
=SUM(OFFSET(A$1,0,0):A5)

OFFSET(reference,rows,cols)
so OFFSET(A$1,0,0) will return A$1
and OFFSET(A$1,1,0) will return A$2
hope that helps you with understanding the offset formula...

Ms Josnah

[teq]

josnah and Bigbas: Thanks!!!!!!! It works great! I'm changing the formulas on my spreadsheets today so I never end up losing another row again.

josnah - the attachment helped - I'm strictly an @sum kind of Excel user so "Offset" is new to me - Thank you for taking the time to work this out for me.