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:

img1

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

img2

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.

img3

  • 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.

img4

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.

img5

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube