Adjust end of range in a Sum formula

[Blair]

I have a simple formula that sums a range of cells. For example: =Sum(C10:C35).

Rather than use the fixed end of range I would like to refer to another cell containing a number to be added to the C10 to compute the end of range. Can you help?

 

[Marcilio]

Hi Blair, Good Evening.


Suppose your control cell is A1.(the cell where you put the end of desired range)


You can use it: =SUM(INDIRECT(CONCATENATE("C1:C",A1)))


Try this one and tell us it it worked for you.


Hope it helps.


-----------------------------------------------------------------------------

Marcílio Lobão

Belo Horizonte, Minas Gerais - Brazil

 

[Blair]

Thanks for your effort but it doesn't give the right answer.


Blair

 

[Hui]

Blair


The above formula works fine on my test data

Did you modify it to suit your data

it should have been:

=SUM(INDIRECT(CONCATENATE("C10:C",A1)))


Can you tell us more about what you have

Can you post a sample file or set of data for us to review

 

[Sajan]

Hello Blair,

I am not completely clear on what you are asking... but if you are looking to change the starting row based on the row value contained in another cell (say A1), you could try:

=SUM(INDIRECT("C"&A1&":C35"))


In this case, if A1 had the value 15, then this results in a sum of the range C15:C35.


If that is not what you are after, please describe your question with an example.


Cheers,

Sajan.


P.S. I noticed you had a duplicate of your post. One of the forum admins will need to delete the duplicate post.

 

[Debraj]

Hello Blair,


I think you need

=SUM(INDIRECT(CONCATENATE("C10:C",10+A1)))


As you said..

another cell containing a number to be added to the C10

Regards

Deb

 

[NARAYANK991]

Hi Blair ,


Why not :


=SUM(OFFSET(C10,,,A1+1))


where A1 contains a number from 0 through any valid upper limit.


Narayan