Adjusting Formulas for Top-Added Rows

David:
If you only insert rows, your idea should work fine. However, if you ever delete a number of rows and add only a few, at some point, your formula range may be shorter than you need.

I regularly do very similar to Col Delane. I put a row of dashes in row 2 and a row of dashes just above the total and anchor my formula on these two rows. Unless I need the worksheet to look pretty (which is rare), I leave the rows with the dashes showing. This gives me an visual of where I need to add the rows and it gives me the immediate visual that my formula is already anchored and I do not need to worry about changing the formula as long as I insert a row between the rows of dashes.

About the only thing you need to watch out for is if you do a countif, in which case you can just subtract two rows.

Good ideas! For me, if the first row is a number, I would just change my formula at the bottom to include it and then subtract it out, i.e. =sum(A1:A15)-A1.

Gary

I have another method that I use in a similar situation. I have a table that is downloaded regularly, and can grow or shrink. I need to be able to reference columns in the list not just for SUM functions, but also for MAX, COUNTIF, and cetera.

So I create a range-name that applies to the data, and define that name such that it will grow or shrink as appropriate.

Assume the data are in column "A", starting at row three. Also that the number of record are not realistically going to approach ten thousand.

Using the range-name manager, I create a new name and type the following in "Refers to":

=OFFSET('Spreadsheet Name'!$A$3,0,0,COUNTA('Spreadsheet Name'!$A$3:$A$10000),1)

If I copied that correctly, what happens is that the range-name will apply to all of the data column A.
One adjustment that might be needed is to subtract from the COUNTA part the number of rows in "A" that contain something other than data. So if rows 1 & 2 have headings, and you want a SUM at the bottom, you will need to subtract three, making the formula:
=OFFSET('Spreadsheet Name'!$A$3,0,0,COUNTA('Spreadsheet Name'!$A$3:$A$10000)-3,1)

To use this in a SUM, simply put the range-name in as so: =SUM(range-name)

What I like is that the range-name can be used anywhere in the workbook.

Tom:
1. The "-" only works if you select the "Transition navigation keys" option under the "Lotus compatibility" section of the Advanced Options - though what this feature has to do with sheet navigation I've got no idea! (I started my spreadsheeting career as a Lotus 123 user so retain a liking for some of its functionality)
2. If the column heading contains a number (e.g. a date) presumably it's there for a reason, so it makes no sense to convert it to text just so the Sum formula will work! That is why I recommend having a row between the header and the first data/value row to which the sum range can be anchored, and populate this cell/row with a text value to describe the unit of measure for the values in the sum range (e.g. $, #, km, lb, kg, etc.)

I tried the - in a spreadsheet but didn't get it to repeat as Col Delane mentioned but it does sound like a good idea. In the main article Allen says to include the top where (column headings) into the sum formula but that this causes a problem if there is a number in the row. If you put a ' in front of the number in the top row, the sum function won't recognize it as a number and ignores it. This still seems like the simplest method.

I think that the best recommendation is to organize the table such way that it will be possible to sum the whole column - SUM(B:B)
(or to use SUMIF or SUMIFS if only some rows should be taken into the sum)

Hi Allen,

Your reference to Ctrl T does not work for me in Excel 07. Rows at the top of a Table do not seem to be picked up as you indicated.

Also re David George - Inserting "$" does not work.

A quick way of ensuring the SUM is always correct is to select the Total cell and press "Alt =" which forces the creation of a new SUM formula, or in a Table, a new SUBTOTAL formula.

I use something that I think is what Col Delane suggests.

Since I'm constantly adding rows, usually withing many rows, I always set up my formulas like this

=SUM($A$1:$A$489)

I think this accomplishes the same thing, doesn't it?

A better variation on the "adjust your SUM formula" approach is to:
1. Insert a row between your column heading and the first value, enter some text for the unit of measure (e.g. $) of the values in the column and then anchor the first cell reference of the Sum range to this cell.
2. Insert a row between the last value and the Sum formula and use it for what I call “fence” by entering “-“ (which repeats the dash character to fill the width of the cell) and anchor the last cell in the Sum range to this cell. This fence is not distracting, avoids having an "orphan" blank cell just hanging there, makes hiding the row unnecessary, doubles as a border and helps identify the Sum as a sub-total or total.