1

Question: I need to update and extend a large table of data with a large number of subheadings. Is there a way/workaround to add a new row to the bottom of a range and have the total formula extend.

Example: I have a range of data at A1:A4 and at cell A5 I have the total formula =SUM(A1:A4). Is there a way to add a new row, or to design the formulas, so that I can add a new row between the final value at A4 and the formula at A5 (moving the formula down to A6), so that the formula extends to =SUM(A1:A5)?

Background: I know that if this was held in a table I could add rows to the bottom of the data and the total columns would extend, but for a number of reasons table data doesn't work with my data. Also, there are totals at both rows and columns, and the table functionality is only on the columns.

My current approach is to add a new row at say 3:3, which would add a new row and extend the formula, but as this has split the data, I then and then cut and paste the rows at 4:5 to be above 3:3, rearranging the data to be back in the same order, but with the sum formulas now extended. This does the trick, but it all seems a little convoluted, and I'm sure a more efficient way.

  • 1
    You can select A4 and insert Row the formula will automatically update – yass Mar 11 '17 at 13:26
2

Instead of =SUM(A1:A4) use the following formula:

=SUM(A1:INDIRECT("A"&(ROW()-1)))

This formula will use indirect to select the inserted row before the place of the formula
If you need to drag it in the same row you can use:

=SUM(A1:INDIRECT("R"&(ROW()-1)&"C"&COLUMN(),FALSE))
This formula will read row and column and sum the cells between A1 and the intersection of Row()-1 and Column()
False to reference R1C1 instead of A1

  • Thanks - this is the sort of approach I was looking for. Very helpful. – lrmoro Mar 12 '17 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.