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:
The formula returns 114, which is the sum of the values in the range A2:A8.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use INDIRECT with INDEX MATCH
Excel: How to Use INDIRECT With Sheet Names
Excel: How to Use SUM with INDEX MATCH
