#### xyzabc198

##### Board Regular
OK, I have an excel spreadsheet.
On the spreadsheet down collum E2-E23 I have numbers ranging from 1 - 50, and then in E24 it adds up all the numbers between E2 and E23 with a simple formula.

Problem is, New lines are added and removed every day, so I have to readjust which collumns it extends between. Is their any way to say to add up lines 3-24 and to increase area 1 line everytime a line is added and decrease by 1 every time a line is removed?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Yard

##### Well-known Member
If your formula is something like

=SUM(E2:E23)

then if a row is inserted between rows 2 and 23 then the formula will be adjusted automatically (and the sum will now be in E25 rather than E24).

Are you asking a different question?

#### xyzabc198

##### Board Regular
well for one particular area (the examples I gave were not accurate)

I have =SUM(D84:D115) and every time I try to add an area between D114 and D115 I have to redo it

#### Richard Schollar

##### MrExcel MVP
If you're inserting a row when E24 is selected then your formula won't update to include the newly added row 24 (at least doesn't seem to in my testing) so you could use:

=SUM(\$E\$3:INDEX(\$E:\$E,ROW()-1))

#### xyzabc198

##### Board Regular
Thanks Richard, worked a miracle :D

#### lapta301

##### Well-known Member
Richard

I have spotted this thread which is of interest to me and wonder if I could trouble you to explain to a pretty basic user how this formula works.

I must say I wondered why xyzabc198 didn't want to include the value in E2 but I am sure he has his reasons.

Many thanks

Lapta301

#### xyzabc198

##### Board Regular
Yes I do have reasons, but it would take me about half an hour to explain lol

#### Richard Schollar

##### MrExcel MVP
Sure:

In the formula =SUM(\$E\$3:INDEX(\$E:\$E,ROW()-1))

It look spretty much like a standard SUM formula eg =SUM(\$E\$3:E24) except that the E24 part is substituted with

INDEX(\$E:\$E,ROW()-1)

So we can deduce that the INDEX function actually returns a cell reference rather than just a simple value. It returns the cell in the E column which is on ROW()-1. ROW() returns the row of the cell in which the formula resides, so subtracting 1 from this gives the cell immediately above the formula cell. Hence the formula works.

Make sense?

#### lapta301

##### Well-known Member
Richard

Many thanks for such a quick and succinct reply which to my surprise I understand.

xyzabc198 - fair comment.

Regards to you both from Surrey, what a small world.

Lapta301

Replies
3
Views
137
Replies
5
Views
421
Replies
6
Views
2K
Replies
3
Views
2K
Replies
51
Views
1K

1,190,582
Messages
5,981,783
Members
439,735
Latest member
Tony_P

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back