Bill has a formula that sums a column in rows 2 through 15, using the SUM function. If he adds a row anywhere at rows 3 through 15, the formula adjusts properly. If he adds a row at the top (just above row 2), the formula does not adjust at all to include the inserted row. Bill wonders if there is a way to make the formula adjust properly and automatically when he adds a row at the top of what is being summed.

There are many schools of thought on how this can be done, each implementing a different technique to accomplish the same thing. One approach is to simply convert your data into a formal table in the worksheet. The easiest way is to select a cell within your data and then press **Ctrl+T**. Excel recognizes your data as a table and if you insert any rows at the top of your table, just under your headings, they are automatically reflected in the summing of the table.

Another approach is to adjust your SUM formula. You proabably are doing a sum of rows 2 through 15 because row 1 has a column heading in it. If this is the case (and if the column heading contains text), then simply adjust your SUM formula so it references rows 1 through 15. Now, when someone adds a new row at row 2, it is automatically included in the sum. Further, the text in the column heading won't affect that sum in the least.

If your headings contain numbers that may actually mess up your sums, then you can try a variation. Insert a blank row at row 2 and adjust your SUM formula so it references rows 2 through 16. (It will be row 16 instead of 15 because you added the new, blank row 2.) Then, hide row 2 so it isn't visible. Now when people add rows at the top, they will actually be adding them at row 3 and the SUM formula will still reference the hidden row 2—adjustments will occur correctly.

You could also use an approach where you adjust the SUM formula to use either the INDIRECT or OFFSET function. Assume, for a moment, that your current SUM formula looks like this:

=SUM(A2:A15)

Change it so that it looks like one of the following:

=SUM(INDIRECT("A2"):A15) =SUM(OFFSET(A1,1,0):A15)

Both of these formulas "anchor" the top cell of the range. When you insert cells at the top of the summed range (before row 2), Excel won't adjust the cell references in either the INDIRECT function or in the OFFSET function. The result is that your SUM formula will always reference the top range cell you want.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (10999) applies to Microsoft Excel 2007, 2010, and 2013.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

When analyzing data, you may need to distill groupings from that data. This tip examines how you can use formulas and ...

Discover MoreGot a list of data from which you want to delete duplicates? There are a couple of techniques you can use to get rid of ...

Discover MoreWhen you create references to cells in other workbooks, Excel, by default, makes the references absolute. This makes it ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2015-09-28 16:32:33

Paul S.

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.

2015-09-28 16:29:16

Paul

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.

2015-09-28 10:43:53

Gary Lundblad

Gary

2015-09-28 08:42:49

Frustrated Fisherman

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.

2015-09-28 07:50:33

Col Delane

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.)

2015-09-28 06:43:47

2015-09-27 08:16:20

barouh

(or to use SUMIF or SUMIFS if only some rows should be taken into the sum)

2015-09-27 02:22:15

Peter Moran

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.

2015-09-26 09:40:24

David George

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?

2015-09-26 08:47:08

Col Delane

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.

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments