Microsoft KB Archive/73586

{|
 * width="100%"|

Using INDEX to Automatically Update Range in SUM Formula

 * }

-

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, 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,  is the address of the first cell of the range to be summed and 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).