Using INDEX() to Automatically Update Range in SUM() Formula

ID: Q73586

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows NT, version 5.0

In Microsoft Excel, a range referenced in a SUM() function (or any function) does not automatically expand to include newly added rows or columns. This situation is true regardless of whether the formula references a range, as in the formula =SUM(A1:E1), or a defined name, as in the formula =SUM(NamedRange).

If you want to reference a range so that it is automatically updated when you add new rows or columns, you can use the INDEX() and SUM() functions in a formula similar to the following :


   =SUM(first_cell:INDEX(column:column,ROW()-1))

In this formula, <first_cell> is the address of the first cell of the range to be summed and <column> is the letter identifying the column of the summed range.

Because the INDEX() function always returns the address of the cell immediately preceding the cell containing the SUM() formula, there is no need to edit the formula when you insert new rows.

If your data is arranged horizontally instead of vertically, use the following formula:


   =SUM(first_cell:INDEX(row:row,COLUMN()-1))

Note that in the above formula the only difference from the previous one is that column:column is changed to row:row, and ROW() is changed to COLUMN().

The above formulas are useful in situations where want to keep a running total of information.

Example

In a new worksheet, type the following:


   A1:  Months             B1: Amounts
   A2:  May                B2: 50
   A3:  June               B3: 50
   A4:  July               B4: 50
   A5:                     B5:

To create a formula such that you can keep a running total of this information, type the following:
```
   A6:  Total              B6: =SUM(B2:INDEX(B:B,ROW()-1)) 
```

Explanation of Formula in B6

The ROW() function returns the row number of the active cell (in this case, B6).

INDEX(B:B,ROW()-1) returns the cell immediately preceding the active cell (in this case, B5).

The entire function is converted to =SUM(B2:B5).

REFERENCES

In Microsoft Excel version 5.0, the equivalent of the "Function Reference" is contained in the Online Help. To access this information, press the F1 key (or select Contents from the Help menu), then choose Reference Information. You should now see Late Breaking Information about Excel and General Reference. Under General Reference, select Worksheet Functions and scroll through the Alphabetical List of Worksheet Functions.

"Function Reference," version 4.0, pages 238, 423

"Function Reference," version 3.0, pages 127, 231

"Functions and Macros," version 2.0 for Windows and OS/2, pages 60, 112

"The Expert," Volume 4, Number 6, June 1991

Additional query words: 2.2 2.20 2.21 3.0 3.00 4.00 4.00a

Keywords :
Version :
Platform :
Issue type :