Excel SUM and INDIRECT Dynamic Range Formula

Screenshot of Excel

Microsoft Excel has some cool tricks and using the SUM and INDIRECT dynamic range formulas are just two ways to easily manipulate the data you have.

SUM - INDIRECT Formula Overview

Using the INDIRECT function in Excel formulas makes it easy to change the range of cell references used in the formula without having to edit the formula itself.

INDIRECT can be used with a number of functions that accept a cell reference as an argument such as the OFFSET and SUM functions.

In the latter case, using INDIRECT as the argument for the SUM function can create a dynamic range of cell references that the SUM function then adds up.

INDIRECT does this by referring to the data in cells indirectly through an intermediate location.

Example: SUM - INDIRECT Formula used to Total a Dynamic Range of Values

This example is based on the data shown in the image above.

The SUM - INDIRECT formula created by using the tutorial steps below is:

=SUM(INDIRECT("D" &E1& ":D" &E2))

In this formula, the nested INDIRECT function's argument contains references to cells E1 and E2. The numbers in those cells, 1 and 4, when combined with the rest of INDIRECT's argument, form the cell references D1 and D4.

As a result, the range of numbers totaled by the SUM function is the data contained in the range of cells D1 to D4 - which is 50.

By changing the numbers located in cells E1 and E2; however, the range to be totaled can be easily changed.

This example will first use the above formula to total the data in cells D1:D4 and then change the summed range to D3:D6 without editing the formula in cell F1.

of 03

Entering the Formula - Options

Create a Dynamic Range in Excel Formulas
© Ted French 

The options for entering the formula include:

  • Typing the above formula directly into cell F1 and pressing the Enter key on the keyboard.
  • Entering the INDIRECT function as an argument using the SUM function's dialog box.

Most functions in Excel have a dialog box, which allows you to enter each of the function's arguments on a separate line without having to worry about syntax.

In this case, the SUM function's dialog box can be used to simplify the formula to a certain extent. Because the INDIRECT function is being nested inside SUM, The INDIRECT function and its arguments must still be entered manually.

The steps below use the SUM dialog box to enter the formula.

Entering the Tutorial Data

  1. Enter the following data into cells D1 to E2.
Cell Data
D1 - 5
D2 - 10
D3 - 15
D4 - 20
D5 - 25
D6 - 30
E1 - 1
E2 - 4

Starting the SUM - INDIRECT Formula - Opening the SUM Function Dialog Box

  1. Click on cell F1 - this is where the results of this example will be displayed.
  2. Click the Formulas tab of the ribbon menu.
  3. Choose Math & Trig from the ribbon to open the function drop down list.
  4. Select SUM in the list to open the function's dialog box.
of 03

Entering the INDIRECT Function - Click to View Larger Image

Entering the Nested INDIRECT Function
 © Ted French

The INDIRECT formula needs to be entered as the argument for the SUM function.

In the case of nested functions, Excel doesn't permit opening the second function's dialog box to enter its arguments.

The INDIRECT function, therefore, must be entered manually in the Number1 line of the SUM Function's dialog box.

  1. In the dialog box, click on the Number1 line.
  2. Enter the following INDIRECT function:
  3. Click OK to complete the function and close the dialog box.
  4. The number 50 should appear in cell F1 since this is the total for the data located in cells D1 to D4.
  5. When you click on cell F1 the complete formula =SUM(INDIRECT("D"&E1&":D"&E2)) appears in the formula bar above the worksheet.

Breaking Down the INDIRECT Function

In order to create a dynamic range in column D using INDIRECT, we must combine the letter D in the INDIRECT function's argument with the numbers contained in cells E1 and E2.

This is accomplished by the following:

  • The ampersand (&) is used to concatenate or join together text data ( in this case the letter D) with a cell reference (E1 and E2).
  • Further, double quotation marks (" ") must surround text data that is being concatenated with cell references.
  • Lastly, the end points of the range are separated by the colon ( : ).

Therefore, the start point of the range is defined by the characters: "D"&E1.

The second set of characters: ":D"&E2 combines the colon with the end point. This is done because the colon is a text character and, therefore, must be included inside of quotation marks.

The third ampersand in the middle is used to concatenate the two parts into one argument:

 "D"&E1 & ":D"&E2
of 03

Dynamically Changing the SUM Function's Range

Dynamically Changing the Formula Range
 © Ted French

The whole point of this formula is to make it easy to change the range totaled by the SUM function without having to edit the function's argument.

By including the INDIRECT function in the formula, changing the numbers in cells E1 and E2 will change the range of cells read by the SUM function.

As can be seen in the image above, this also results in the formula's answer located in cell F1 changing as it totals the new range of data.

  1. Click on cell E1.
  2. Type the number 3.
  3. Press the Enter key on the keyboard.
  4. Click on cell E2.
  5. Type the number 6.
  6. Press the Enter key on the keyboard.
  7. The answer in cell F1 should change to 90, which is the total of the numbers contained in cells D3 to D6.
  8. Further test the formula by changing the contents of cells B1 and B2 to any numbers between 1 and 6.

INDIRECT and the #REF! Error Value

The #REF! error value will appear in cell F1 if the INDIRECT function's argument:

  • Is not a valid cell reference.
  • Contains an external reference to a different workbook and that workbook is not open.
  • Refers to a cell range outside of the worksheet's limits (row 1,048,576 or column XFD).