SUM - INDIRECT Formula Tutorial 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.

Entering the Formula - Options

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
2. 
   CellData 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 on the Formulas tab of the ribbon menu
3. Choose Math & Trig from the ribbon to open the function drop down list
4. Click on SUM in the list to open the function's dialog box