This Excel Trick Lets You Sum a Number of Cells With a Variable Range

How to Sum a Number of Cells Using a Variable Range

You can embed the INDIRECT function as an argument inside of the SUM function to create a variable range of cell references for the SUM function to add. The INDIRECT function does this by referencing the range of cells indirectly, through an intermediate cell reference.

The format of both functions used together looks like this:

=SUM(INDIRECT("D1":D4)

This locks the range to start at D1 and allows D4 to change if you insert or delete any cells in the D column.

You can also use other cells in the spreadsheet to modify the cell references. For example, if you use E1 to reference the first cell of the range and E2 to reference the last cell of the range, the formula looks like this:

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

By changing the numbers located in cells E1 and E2, you can modify the range in the formula without having to manually edit the formula.

Try the SUM and INDIRECT Functions

Create a spreadsheet to test the SUM and INDIRECT functions yourself. Begin by creating a blank spreadsheet and entering the following data into columns D and E:

Cell Data
 D1 - 5
 D2 - 10
 D3 - 15
 D4 - 20
 D5 - 25
 D6 - 30
 E1 - 1
 E2 - 4

Next, create the formula in cell F1. Here's how:

1. Select cell F1. This is where the result of this example will display.
2. Select Formulas.
3. Choose Math & Trig to open the function drop-down list.
4. Select SUM in the list to open the SUM Function Arguments dialog box.

Nest the INDIRECT Function into the SUM Function

Next, enter the INDIRECT function into the SUM function using this dialog box.

1. In the Number1 field, enter the following INDIRECT function:

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

2. Select OK to complete the function and close the dialog box.
3. The number 50 appears in cell F1. This is the sum of cells D1 to D4.
4. When you select cell F1, the formula field shows:

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

When you understand how to format the SUM and nested INDIRECT functions, you'll be able to type the entire function shown in step 4 directly into the result cell (in this example, cell F1).

Build the Argument for the INDIRECT Function

In the example above, the references to cells E1 and E2 are outside of the quotes. This, combined with the & concatenate symbol, embeds whatever is contained inside of cells E1 and E2 into the formula itself.

Here's how the elements of the formula work:

• & is used to join together text data (in this case the letter D) with a cell reference (E1 and E2) that contains a number value.
• " " must surround text data inside of the cell references (in this example, the letter D).
• : is a separator between the first cell and the last cell in the SUM range.

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

The endpoint of the range is defined by the characters: ":D"&E2

The colon needs to be included inside the quotation marks.

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

"D"&E1 & ":D"&E2