Keeping SUM range when adding a new row to the 1st place

[sduttonusa]

I have numbers across rows 10 to 25. Underneath (say row 45), I get the SUM of the columns above =SUM(A10:A25), =SUM(B10:B25), etc. When I add a new row somewhere in the middle of the rows, the bottom of the column SUM takes into account the new row =SUM(A10:A26), =SUM(B10:26), etc.

But if I add a new row in the 1st place position, row 10, the SUM below becomes =SUM(A11:A26), =SUM(B11:B26) . . . row 10 doesn't get included. This even happens if I create a new row in the middle, and then later move it up to the 1st position.

Is there a way around this?

 

[Robby19]

Convert the range to a Table. Highlight all of your data then on the excel ribbon, click Insert > Table. You can then add a "Total Row" through the Design ribbon which can be used to sum all data in specific columns/rows. That way when you add data, it will automatically update.

You could also just use the =Sum() formula and select the header under which data you want to sum.

 

[markmzz]

I have numbers across rows 10 to 25. Underneath (say row 45), I get the SUM of the columns above =SUM(A10:A25), =SUM(B10:B25), etc. When I add a new row somewhere in the middle of the rows, the bottom of the column SUM takes into account the new row =SUM(A10:A26), =SUM(B10:26), etc.

But if I add a new row in the 1st place position, row 10, the SUM below becomes =SUM(A11:A26), =SUM(B11:B26) . . . row 10 doesn't get included. This even happens if I create a new row in the middle, and then later move it up to the 1st position.

Is there a way around this?

Maybe the formula below can helps:

=SUM(INDEX(A:A,10):A27)

Markmzz

 

[sduttonusa]

Thank you for your quick replies, Robby19 and markmzz. I would like to try to solve this issue using one of the formulas that you suggested. (Converting my data to a table messed up my column widths, colors, heading titles, etc.). I've posted a small table below so I can better understand the designations within the formulas you suggest. Robby19, I wasn't able to figure out how to SUM a selected Header. markmzz, the SUM(INDEX*** formula worked fine, but I quickly discovered that if I add a new row at the bottom of the table, I have the same problem: the last row is ignored. Is there a tweak to the SUM(INDEX*** formula that I can add?


A4 REIMBURSEMENTS B4 JAN C4 FEB D4 MAR
[TABLE="width: 500"]
<tbody>[TR]
[TD="class: xl69, width: 239, align: right"]A5 Shaum[/TD]
[TD="class: xl66, width: 87, align: right"]100[/TD]
[TD="class: xl66, width: 87, align: right"]100[/TD]
[TD="class: xl66, width: 87, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]A6 Tagargulias[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]300[/TD]
[TD="class: xl66, align: right"]400[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]A7 Thompson[/TD]
[TD="class: xl66, align: right"]300[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]A8 Vorm[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl66, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]A9 TOTAL[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]800[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]700[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]900[/TD]
[/TR]
</tbody>[/TABLE]

Row 4 is my Header Row. A5 is where a new first row will be placed, and after A8 is where a new row at the bottom would be placed. I may find myself being forced to use a table because this isn't the only set of numbers on the worksheet. There will be new rows created above this particular set of number (but perhaps your formulas will automatically make accommodations for the movement of this set of numbers up and down the page.

 

[markmzz]

Thank you for your quick replies, Robby19 and markmzz. I would like to try to solve this issue using one of the formulas that you suggested. (Converting my data to a table messed up my column widths, colors, heading titles, etc.). I've posted a small table below so I can better understand the designations within the formulas you suggest. Robby19, I wasn't able to figure out how to SUM a selected Header. markmzz, the SUM(INDEX*** formula worked fine, but I quickly discovered that if I add a new row at the bottom of the table, I have the same problem: the last row is ignored. Is there a tweak to the SUM(INDEX*** formula that I can add?

A4 REIMBURSEMENTS B4 JAN C4 FEB D4 MAR
[TABLE="width: 500"]
<tbody>[TR]
[TD="class: xl69, width: 239, align: right"]A5 Shaum[/TD]
[TD="class: xl66, width: 87, align: right"]100[/TD]
[TD="class: xl66, width: 87, align: right"]100[/TD]
[TD="class: xl66, width: 87, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]A6 Tagargulias[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]300[/TD]
[TD="class: xl66, align: right"]400[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]A7 Thompson[/TD]
[TD="class: xl66, align: right"]300[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl69, align: right"]A8 Vorm[/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl66, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]A9 TOTAL[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]800[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]700[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]900[/TD]
[/TR]
</tbody>[/TABLE]
Row 4 is my Header Row. A5 is where a new first row will be placed, and after A8 is where a new row at the bottom would be placed. I may find myself being forced to use a table because this isn't the only set of numbers on the worksheet. There will be new rows created above this particular set of number (but perhaps your formulas will automatically make accommodations for the movement of this set of numbers up and down the page.

Hi!

Try this in A9 and copy to the right:

=SUM(INDEX(B:B,5):OFFSET(B9,-1,))


[TABLE="class: grid, width: 297"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]REIMBURSEMENTS[/TD]
[TD]JAN[/TD]
[TD]FEV[/TD]
[TD]MAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Shaum[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Tagargulias[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Thompson[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Vorm[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]TOTAL[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*****************[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[sduttonusa]

You meant, "Try this in B9, correct?" When I put it in B9, it works.

 

[sduttonusa]

If it's not too much of a problem, so I truly understand what is happening in the formula, could you take a moment and break the formula down to explain it? What does the INDEX formula do? The first instance of "B", after INDEX, that refers to the column, correct? Why the semi-colon before B,5 (and why the comma). The second semi-colon is there to give the range for the SUM to be calculated, correct? What does the OFFSET do?

 

[markmzz]

Hi!

Try this in B9 and copy to the right:

=SUM(INDEX(B:B,5):OFFSET(B9,-1,))

Markmzz

You meant, "Try this in B9, correct?" When I put it in B9, it works.

Yes, you are right. Look the text above (in red).

If it's not too much of a problem, so I truly understand what is happening in the formula, could you take a moment and break the formula down to explain it? What does the INDEX formula do? The first instance of "B", after INDEX, that refers to the column, correct? Why the semi-colon before B,5 (and why the comma). The second semi-colon is there to give the range for the SUM to be calculated, correct? What does the OFFSET do?

Lets go:

1) In the part INDEX(B:B,5) - the B:B refers to all column B, the 5 refers to the fifth row of the column B (so the result is B5 - the start (first) part of the final range of the argument of the SUM function).

2) The second semi-colon is to link the start (first) part of the range (B5) with the end part of the range (the result of the OFFSET function).

3) The OFFSET function (look at the link below for more details - OFFSET(B9,-1,)) offset for cell B9 one cell above (-1 - then we have B8).

https://support.office.com/en-us/article/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

4) So, the final result of the argument of SUM function is B5:B8.

I hope that this helps.

Markmzz

 

[sduttonusa]

Perfect . . . thank you so much. So the OFFSET "references" a particular cell. That makes sense.

 

[markmzz]

Perfect . . . thank you so much. So the OFFSET "references" a particular cell. That makes sense.

You are welcome and I'm glad to help.

Markmzz