Summing a variable range

[Ram B]

I need to create a formula for summing cells say B2 through J2 with results
in A2 so I use the formula

=sum(B2:J2)

As you see I am summing 9 cells.

What I want to do is able to type a number in Cell A1 and sum the B2 to A1
number of cells to the right of B2.

Any help will be appreciated.

 

[Stefi]

=SUM(OFFSET(B2,0,0,1,A1))

Regards,
Stefi

„Ram B†ezt írta:

 

[John C]

=SUM(INDIRECT("B2:"&LEFT(ADDRESS(1,A1+1,2),FIND("$",ADDRESS(1,A1+1,2))-1)&"2"))

 

[Ram B]

Thanks it works

=SUM(OFFSET(B2,0,0,1,A1))

Regards,
Stefi

„Ram B†ezt írta:

 

[Peo Sjoblom]

Here's a non volatile solution

=SUM(B2:INDEX(B2:IV2,A1))

--


Regards,


Peo Sjoblom

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Ram B†ezt írta:

 

[Stefi]

Why do you call it a non volatile solution? As far as I know "volatile" means
that the expression is recalculated each time a Calculation occurs. It
applies to your formula as well, doesn't it?

Regards,
Stefi


„Peo Sjoblom†ezt írta: