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

Make the SUM function smarter with a variable range

Image of a spreadsheet showing a closeup of the sum of a column of numbers

DNY59/Getty Images 

In Excel, you can sum a number of cells using a variable range with the INDIRECT function. The INDIRECT function automatically updates the range of cells you've referenced without manually editing the formula itself. You can use the INDIRECT function with any number of Excel functions, but the most common (and useful) is when you use the SUM function.

Note: The information in the article applies to Excel versions 2019, 2016, 2013, 2010, and Excel for Mac.

01
of 05

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.

02
of 05

Try the SUM and INDIRECT Functions

Create a Dynamic Range in Excel Formulas

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.
03
of 05

Nest the INDIRECT Function into the SUM Function

Entering the Nested INDIRECT 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).

04
of 05

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 end point of the range is defined by the characters: ":D"&E2

The colon needs to be included inside of the quotation marks.

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

"D"&E1 & ":D"&E2
05
of 05

The SUM Function Variable Range

Dynamically Changing the Formula Range

The INDIRECT function nested inside of the SUM function makes it easy to change the start and end of the range totaled by the SUM function, without having to edit the function itself.

Using the concatenation technique in step 3, use the numbers stored in cells E1 and E2 in the spreadsheet to change the range of cells for the SUM function.

Test this in your own spreadsheet by modifying cells E1 and E2 and watch how the SUM function results change.

  1. Select cell E1.
  2. Type the number 3.
  3. Press Enter.
  4. Select cell E2.
  5. Type the number 6.
  6. Press Enter.
  7. The answer in cell F1 changes to 90. This is the sum of the numbers contained in cells D3 to D6.

To see the INDIRECT function in action, insert a new cell into cell D3. This shifts all of the other cells down. The new sum is the total of cells D3 to D7. The INDIRECT function dynamically changes the entire range to include the entire list you initially entered, plus the new cell you inserted as well.

The #REF! Error Value

The #REF! error value appears in cell F1 if the INDIRECT function:

  • Contains an invalid cell reference.
  • Contains an external reference to a different worksheet that doesn't exist.
  • Refers to a cell range outside of the worksheet's limits.
  • Refers to a cell that is blank.