Table of Contents
The INDIRECT function in Excel allows you to refer to a cell or range of cells using a text string as a reference. By combining this function with the SUM function, you can dynamically change the range of cells being summed based on the text string input. This can be useful for creating flexible and customizable formulas that adjust to changes in the data. Simply enter the cell reference as a text string within the INDIRECT function, and use the SUM function to calculate the sum of the referenced cells. This allows for efficient and organized data analysis in Excel.
Excel: Use INDIRECT with SUM
You can use the INDIRECT function with the SUM function in Excel to sum values in a specific range that is specified in one particular cell.
The following examples show two different ways to use the INDIRECT and SUM functions together in practice with the following column of values in Excel:
Example 1: Use INDIRECT & SUM with Cell References in One Cell
Suppose we would like to use the INDIRECT and SUM functions to sum the values in the cells A2 through A8.
One way to do so is by specifying this range in cell C1 and then typing the following formula into cell D1:
=SUM(INDIRECT(C1))
The following screenshot shows how to use this formula in practice:
The formula returns 114, which is the sum of the values in the range A2:A8.
We can manually confirm this is correct by calculating the sum of values in these cells:
Sum of A2:A8 = 14 + 15 + 20 + 22 + 18 + 13 + 12 = 114
Example 2: Use INDIRECT & SUM with Cell References in Multiple Cells
Suppose once again we would like to use the INDIRECT and SUM functions to sum the values in the cells A2 through A8.
One way to do so is by specifying the starting cell of the range in cell C1 and the ending cell of the range in cell C2 then typing the following formula into cell D1:
=SUM(INDIRECT(C1&":"&C2))
The following screenshot shows how to use this formula in practice:
Additional Resources
The following tutorials explain how to perform other common operations in Excel: