Summing Values In a Range Specified By Indirect Cell References in Excel

In this article we will learn about how we can sum the value to a specified range by indirect cell references in Excel.

To sum values in a range specified by indirect cell references in Excel, we will use a combination of SUM & INDIRECT functions to get the output.

SUM: Adds all the numbers in a range of cells

Syntax: =SUM(number1,number2,...)

There can be maximum 255 arguments.Refer below shown screenshot:


INDIRECT: Returns the reference specified by a text string.

Syntax: =INDIRECT(ref_text,A1)

Example: If value in cell A1 contains 10, B1 contains A1 & we use INDIRECT function in cell C1=INDIRECT(B1), then result would be 10


Let us take an example:-


Column A contains numbers.Column B contains specified range of cell references that need to be totaled.Cell B1 is the starting range & B2 is the ending range.


  • In cell D2, the formula would be
  • =SUM(INDIRECT("A"&B1&":A"&B2))
  • Press Enter on your keyboard.
  • The function will return the total values to range specified by indirect cell reference.


The above shown formula will add the sum of values as specified in column B. If we change the value in cell B2 from 3 to 4 then the formula will calculate the result accordingly.


Users are saying about us...

  1. Good day,
    I tried this formula in a workheet and the result I got was volatile. There are no blanks between the row range. What went wrong please?
    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube