excel auto sum question and other

[Michele Bishop]

We have a spreadsheet created and the boss wants the totals to be the top row. I am having issues with Auto Sum as every time I add a new row, I then have to update the formula to include that new row. I am sure there is an easier way to do this but I have been unable to find. Any help would greatly be appreciated.

 

[VoG]

Hello and welcome to MrExcel.

You can use a formula like

=SUM(C2:C1000)

Excel will ignore any blank rows below your data.

 

[Michele Bishop]

I tried that - at least I thought I did. But when I enter a new row at the top (boss wants most current info entered on top) the formula changes to SUM=C3:C1000. If I entered a new row at the bottom, I do not have the issue. Hope that makes sense.

 

[scottylad2]

Excel Workbook
	I	J
1	TOTAL	842
2		10
3		25
4		35
5		100
6		74
7		64
8		61
9		58
10		55
11		52
12		49
13		46
14		43
15		40
16		35
17		35
18		35
19		25
Sheet1

Don't use the Autosum, just type and highlight the cells you want to include

 

[Michele Bishop]

So I have to redo the formula each time to include the new row? The new row would replace the current row 2. When adding now, it does not included in the total formula in row 1. To note - there are 30 columns that I have to update each time which is why I had hoped there was an easier way.

 

[xlHammer]

Try something like;

=SUM(OFFSET(C$1:C1000,1,0))

 

[scottylad2]

Does your totals have to be at the top of the row your summing? you could put in A;1 =Sum(B:B) and that will update even if you put a new row in. other than that you would maybe get away with it if you made the row you were summing into a dynamic range which would also allow new rows to be added