summing a volitile number of cells

[Shunt]

I've got a row of numbers A2:K2. I want a formula that will sum from A2 to
the number of cells I enter in A1.

So if I enter "3" in cell A1, the formula will sum from A2 to C2.
and if I enter "5" in cell A1, it will sum from A2 to E2.

I've been fiddling around in R1C1 but not getting it to work...

[Chip Pearson]

Try

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Shunt" <Shunt@discussions.microsoft.com> wrote in message
news:73DCF1CD-F8C3-43F0-BE31-88EE780DE2F7@microsoft.com...
> I've got a row of numbers A2:K2. I want a formula that will
> sum from A2 to
> the number of cells I enter in A1.
>
> So if I enter "3" in cell A1, the formula will sum from A2 to
> C2.
> and if I enter "5" in cell A1, it will sum from A2 to E2.
>
> I've been fiddling around in R1C1 but not getting it to work...

[Shunt]

Thanks Chip, I was cruising your web site when you for answers when you
responded in person.
THX

"Chip Pearson" wrote:

> Try
>
> =SUM(OFFSET(B2,0,1,1,A2))
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Shunt" <Shunt@discussions.microsoft.com> wrote in message
> news:73DCF1CD-F8C3-43F0-BE31-88EE780DE2F7@microsoft.com...
> > I've got a row of numbers A2:K2. I want a formula that will
> > sum from A2 to
> > the number of cells I enter in A1.
> >
> > So if I enter "3" in cell A1, the formula will sum from A2 to
> > C2.
> > and if I enter "5" in cell A1, it will sum from A2 to E2.
> >
> > I've been fiddling around in R1C1 but not getting it to work...
>
>
>

[Bob Phillips]

A non-volatile alternative for you

=SUM(A2:INDEX(2:2,A1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Shunt" <Shunt@discussions.microsoft.com> wrote in message
news:4FDE99D8-1670-40D4-9B1F-305E82D4B420@microsoft.com...
> Thanks Chip, I was cruising your web site when you for answers when you
> responded in person.
> THX
>
> "Chip Pearson" wrote:
>
> > Try
> >
> > =SUM(OFFSET(B2,0,1,1,A2))
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Shunt" <Shunt@discussions.microsoft.com> wrote in message
> > news:73DCF1CD-F8C3-43F0-BE31-88EE780DE2F7@microsoft.com...
> > > I've got a row of numbers A2:K2. I want a formula that will
> > > sum from A2 to
> > > the number of cells I enter in A1.
> > >
> > > So if I enter "3" in cell A1, the formula will sum from A2 to
> > > C2.
> > > and if I enter "5" in cell A1, it will sum from A2 to E2.
> > >
> > > I've been fiddling around in R1C1 but not getting it to work...
> >
> >
> >

[Trevor Shuttleworth]

=SUM(OFFSET(A2,,,,A1))

Regards

Trevor


"Shunt" <Shunt@discussions.microsoft.com> wrote in message
news:73DCF1CD-F8C3-43F0-BE31-88EE780DE2F7@microsoft.com...
> I've got a row of numbers A2:K2. I want a formula that will sum from A2
> to
> the number of cells I enter in A1.
>
> So if I enter "3" in cell A1, the formula will sum from A2 to C2.
> and if I enter "5" in cell A1, it will sum from A2 to E2.
>
> I've been fiddling around in R1C1 but not getting it to work...

[Peo Sjoblom]

One way

=SUM(A2:INDEX(A2:K2,A1))

it's non volatile as opposed to OFFSET and INDIRECT

Regards,

Peo Sjoblom


"Shunt" wrote:

> I've got a row of numbers A2:K2. I want a formula that will sum from A2 to
> the number of cells I enter in A1.
>
> So if I enter "3" in cell A1, the formula will sum from A2 to C2.
> and if I enter "5" in cell A1, it will sum from A2 to E2.
>
> I've been fiddling around in R1C1 but not getting it to work...