inserting a row and adding the new rows?

**mkabs11** · 03-17-2011, 02:32 PM

Hi all, this is my first time posting here. I don't know how to explain this but here goes. I am trying to sum a range and and keep the count of the range to be the same after inserting a new row. I would like to sum the last 5 row only. Is this possible without using VBA/Marco's?

Thank you,

Mark

**JBeaucaire** · 03-17-2011, 03:07 PM

The answer could be greatly affected by your sheet design. If you post the exact sheet showing where this formula is supposed to go, and perhaps instructions on how you "add rows", then I'm sure we could get it right first try.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**mkabs11** · 03-17-2011, 03:25 PM

Sorry about that, i forgot to attach the excel sheet.

**Osvaldo Palmeiro** · 03-17-2011, 03:55 PM

Hi

maybe this one could work

=SUM(INDIRECT(ADDRESS(ROW()-5,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))

**mkabs11** · 03-17-2011, 04:10 PM

Thank you very much for the quick reply. I don't think I explained myself very clearly. Let me try again. I am trying to sum column B range b2:b7 as in the worksheet. After i insert another row say 8 I would like the sum to be range b3:b8.

thank you

**Osvaldo Palmeiro** · 03-17-2011, 04:27 PM

Sorry, in your OP you said last 5 rows but in B2:B7 there are 6 rows

try this please

=SUM(INDIRECT(ADDRESS(ROW()-6,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))

**mkabs11** · 03-17-2011, 04:54 PM

Sweet!!! that is just what the doctor ordered!! thank you very much. I am trying to learn and I have been reading this forum for weeks trying to figure these things out.