excel formulas - auto update SUM formula based on range, when rows added/deleted

0
votes

I have a workbook with a SUM formula (in E15), which adds together the values entered in the 4 cells below (E16:E19). Below that list (E20 onwards) is another SUM formula that adds together the next 4 cells (E21:E24). This repeats throughout the workbook to calculate sub-totals of a range of items, however, I will need to add and delete rows so the total number of cells to be added to each formula will not always be 4.

How can I ensure the SUM formula updates itself to include all of the values entered below when a new row is added or one deleted? Could the formula search cells below and stop when it reaches the next formula?

EG: Formula in E15 =SUM(E16:E19) Then I may want to add three rows at E16, delete one somewhere in the middle.. Formula in E15 now reads =SUM(E19:E21), but I need it to read =SUM(E16:E21).

This needs to happen automatically so users don't have to manually amend the SUM range every time.

Any help much appreciated.

2
vbaexcelexcel-formula
So you want it to always go from the row below the formula to the next formula? Is there a common title in another column like "SUBTOTAL"? – Scott Craner
Thats right Scott. The SUM formula and it's value's to be added are all in the same column, the name of this column (written in E5) is "INITIAL SUMS". The common title in the cell adjacent to each SUM formula (one cell to the left) just reads "Sub-total". – user6774926
In your comments to both answers you stated that mine worked and the other did not, but you mark the other as correct. Was this an error or did something not work in my formula? – Scott Craner

2 Answers

1
votes

Put this in every sub-total cell in Column E:

=SUM(INDEX(E:E,ROW()+1):INDEX($E:$E,ROW() + IFERROR(MATCH("Sub-total",INDEX(D:D,ROW()+1):$D1004006,0)-1,MATCH("Project Total",INDEX(D:D,ROW()+1):$D1004006,0)-1)))

It will set the area from the row beneath the formula to the next time that Sub-total in Column D

As per your comment I ammended the formula to include a look for the words "Project Total" In Column D. If the value in Column D that indicates the sum of all the subtotals in Column E is spelled different make the change to what it is .

0
votes

If you are worried about users modifying the workbook, you can still sum the range but protect the worksheet. It's a lot easier and sounds like what you need.

I don't know your version of Excel, but you should be able to find it under the Review tab. Good luck.