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

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

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

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

Example

1. 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:
   
   

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