How do I sum a column of values when rows are inserted or deleted outside the formula?

[Aretesys]

If I have a spreadsheet that sums a column of the rows above it, I obviously have to refer to the starting row/cell, say A3. I then refer to the ending row/cell, say A10.
My problem is that my users will insert new rows above row 3, but my formula says start at A3. If there is a value to be summed in A2, it's ignored because the formula starts with A3.
The same problem occurs when the users insert a row below row 10; the value in row/cell A11 is ignored. I know Excel can adjust if they insert a row in between the values in the formula, for example a new row above row 6. But they can't be counted on to do that, despite my entreaties.
I don't want a VBA solution; I'm sure there is a function I'm missing. I've looked at Dynamic Ranges, but I don't think that solves my problem.
So how can I sum all the rows that have user data without checking the formula each time I publish the spreadsheet?
I'm using Excel 2007, but will be switching to 2013 in a day or two.
I know this has to be one of the dumbest questions on the forum!
Thanks so much for your help!

 

[Roger Govier]

Hi

I would hide row 3 and then hide row 11 and make my formula = Sum(A3:A11)
Nay insertions would then have to be between row 4 and row 10, and the formula will always work.

 

[Aretesys]

Hi

I would hide row 3 and then hide row 11 and make my formula = Sum(A3:A11)
Nay insertions would then have to be between row 4 and row 10, and the formula will always work.

What a clever idea! I never would have thought of that. Thanks so much!
I would still like to hear from others. I'm particularly interested in seeing if there is a way of using Named Ranges that can flex to include rows inserted above or below the named range. That would seem to go against the idea of selecting a certain set of cells, but the idea of a "flexible range" of some sort is intriguing.

 

[XOR LX]

Hi,

It's not quite clear how your sum range is to be determined.

What can be said about the first and last cells in that range? For example, will the first reference always be the first non-empty cell in that column? And the last reference always the last non-empty cell in that column?

Regards

 

[Aretesys]

Hi,

It's not quite clear how your sum range is to be determined.

What can be said about the first and last cells in that range? For example, will the first reference always be the first non-empty cell in that column? And the last reference always the last non-empty cell in that column?

Regards

The first reference is not always the first non-empty cell in that column; in fact, it will probably never be, as it sits right below a heading cell.
The last reference could be made into the last non-empty cell, but I would prefer not to rely on that being the case
The rows represent projects; each row has information about a project, including how many estimated hours it will take. At the bottom of that column would be a SUM field that would show the total estimated hours for all the projects listed (usually about 14).
My problem is that my Project Managers will add a new row when they are assigned a new project. They forget what I told them about inserting in the middle of the range, and add it above the first row or below the last. Then my range is incomplete, and my totals will be wrong.
I hope that helps. Thanks for your response.

 

[skorpionkz]

You can include the Header (if it is not a number) in Sum. Then if project manager will add new project it always will be withing the Sum range.
If you can make 1 row space between projects table and SUM formula, then include the empty row below the table in the sum range as well. Then if project manager will add new project just below the last added project, formula will adjust itself as well.

 

[XOR LX]

Yes. SUM will ignore both blanks and text entries within the range. So, providing we can assume that using A2, say, as the lower range reference will always be appropriate, we can then also detect the last-used cell in that column to use for the upper range reference.

This will work providing you do not have other numerical values in that range which you do not wish to form part of the sum - is this the case?

Regards

 

[Roger Govier]

Hi
If you want to use a named range, then
If there are not going to be any blank cells with your range, you could define the range as

Amount =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)) (change sheet and column reference to suit).

or if there will be blank cells you could use

Amount =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(LOOKUP(99^99,Sheet1!$A:$A),Sheet1!$A:$A,0))

Then =Sum(amount) will give you your result

 

[skorpionkz]

Amount =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)) (change sheet and column reference to suit).

Amount =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(LOOKUP(99^99,Sheet1!$A:$A),Sheet1!$A:$A,0))

I am not sure, but if this range will not include the totals below the time table?

And actually hot to define above range? :D

 

[Aretesys]

Hi
If you want to use a named range, then
If there are not going to be any blank cells with your range, you could define the range as

Amount =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)) (change sheet and column reference to suit).

or if there will be blank cells you could use

Amount =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(LOOKUP(99^99,Sheet1!$A:$A),Sheet1!$A:$A,0))

Then =Sum(amount) will give you your result

Thank you Roger; that's another intriguing possibility. I'll need to investigate more about how the INDEX and LOOKUP functions work in this context, but I'll learn in the process. Thanks for taking the time.