Summing a variable range of columns

In cell C1
=SUM(INDIRECT("B1:B"&A1)
copy down through C2:C3

--
Regards
Roger Govier
"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:fu7he1hj9htbl1h5q2nnkiobrroe8e0n1a@4ax.com...
> Can anyone suggest a formula to do the following
>
> Where:
>
> A1 = 6
> A2 = 3
> A3 = 9
>
> B1:J1 = 1,2,3,4,5,6,7,8,9
> B2:J2 = 1,2,3,4,5,6,7,8,9
> B3:J3 = 1,2,3,4,5,6,7,8,9
>
> Formulae in C1:C3 to do the following:
>
> In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
> range B1:J1 and result in the number 21
>
> In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
> range B1:J1 and result in the number 6
>
> In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
> range B1:J1 and result in the number 45
>
> Usual TIA
>
>
> Regards
>
>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

On Thu, 28 Jul 2005 13:07:22 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>Ron,
>
>My apologies for confusing the matter. I should have referred to
>column K rather than column C
>
>Thanks for your input.
>
>Rgds

Oops, I saw your subsequent posting and see how you want to determine 1, vs
1&4, vs 1&4&7.

Assuming you're being quite specific, the following formula should do what you
describe:

=SUMPRODUCT((COLUMN(B1:J1)<=CHOOSE(A1/3,2,5,8))*
(MOD(COLUMN(B1:J1)-2,3)=0)*B1:J1)


--ron

On Thu, 28 Jul 2005 10:20:24 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>In cell C1
>=SUM(INDIRECT("B1:B"&A1)
>copy down through C2:C3


Roger,

Thanks for the quick repsonse. I apologise for sewing some confusion.

I should have said that I want the results in K1:K3 and that it is
Cells B1, C1, D1, E1 etc, which have the values 1, 2, 3, 4.. etc
respectively. i.e. I'm adding a variable range of either 3, 6 or 9
cells depending on the value in A

I also need an additional formula which adds the first, fourth or
seventh cells depending on the value in A.

i.e. given the earlier data.

In say L1 (where A1= 6) I need to add B1 and E1 = 5
In say L2 (where A1= 3) I just need B1 = 1
In say L3 (where A1= 9) I need to add B1 and E1 and H1= 12

Any ideas / suggestion gratefully received.

Regards

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 28 Jul 2005 10:13:36 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>Can anyone suggest a formula to do the following
>
>Where:
>
>A1 = 6
>A2 = 3
>A3 = 9
>
>B1:J1 = 1,2,3,4,5,6,7,8,9
>B2:J2 = 1,2,3,4,5,6,7,8,9
>B3:J3 = 1,2,3,4,5,6,7,8,9
>
>Formulae in C1:C3 to do the following:
>
>In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
>range B1:J1 and result in the number 21
>
>In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
>range B1:J1 and result in the number 6
>
>In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
>range B1:J1 and result in the number 45
>
>Usual TIA
>
>
>Regards
>
>
>
>__
>Richard Buttrey

It's not possible as the conditions you specify set up a circular reference; in
addition, you cannot have both the number '2' and a formula in the same cell
(column C).

If you would care to purt your formulas in other than C1:C3, you could use the
formula:

=SUM(OFFSET(B1,,,,A1))

for Row 1 and copy/drag down as needed.

If you want something else, or really want to use a circular reference, I'd
need more details of exactly what you are trying to accomplish.


--ron

Richard
It wasn't you causing confusion, it was me misreading.
In cell K1
=SUM(B1:OFFSET(B1,0,A1-1))
and copy down


--
Regards
Roger Govier
"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:ohahe15v07ierj1mech47gugdg8mavvt30@4ax.com...
> On Thu, 28 Jul 2005 10:20:24 +0100, "Roger Govier"
> <roger@technologyNOSPAM4u.co.uk> wrote:
>
>>In cell C1
>>=SUM(INDIRECT("B1:B"&A1)
>>copy down through C2:C3
>
>
> Roger,
>
> Thanks for the quick repsonse. I apologise for sewing some confusion.
>
> I should have said that I want the results in K1:K3 and that it is
> Cells B1, C1, D1, E1 etc, which have the values 1, 2, 3, 4.. etc
> respectively. i.e. I'm adding a variable range of either 3, 6 or 9
> cells depending on the value in A
>
> I also need an additional formula which adds the first, fourth or
> seventh cells depending on the value in A.
>
> i.e. given the earlier data.
>
> In say L1 (where A1= 6) I need to add B1 and E1 = 5
> In say L2 (where A1= 3) I just need B1 = 1
> In say L3 (where A1= 9) I need to add B1 and E1 and H1= 12
>
> Any ideas / suggestion gratefully received.
>
> Regards
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

On Thu, 28 Jul 2005 13:07:22 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>Ron,
>
>My apologies for confusing the matter. I should have referred to
>column K rather than column C
>
>Thanks for your input.
>
>Rgds

You're welcome.

K1: =SUM(OFFSET(B1,,,,A1))

should work just fine. Copy/Drag down as needed.

You also asked about, 1; 1&4; 1&4&7 depending on contents of A1.

I'm not certain of the relationship between A1 and your three choices.

Since 1-9 are already spoken for, I suppose you could use A, B, & C for your
three choices.

Or did you have something else in mind?


--ron

Roger,

Brilliant. Thanks.

Sorry to be a pest but do you have any thoughts on the second example,
where I want to sum either the 1st, 1st & 4th or 1st, 4th & 7th
columns in the B:J range depending on the A value


TIA


On Thu, 28 Jul 2005 11:50:08 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>Richard
>It wasn't you causing confusion, it was me misreading.
>In cell K1
>=SUM(B1:OFFSET(B1,0,A1-1))
>and copy down

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard
In L1
=CHOOSE(--SUM(A1>=7,A1>=4)+1,B1,(B1+E1),(B1+E1+H1))
copy down

--
Regards
Roger Govier
"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:pbihe1l293sn7r6koh3vlv45f4k5r8sm69@4ax.com...
> Roger,
>
> Brilliant. Thanks.
>
> Sorry to be a pest but do you have any thoughts on the second example,
> where I want to sum either the 1st, 1st & 4th or 1st, 4th & 7th
> columns in the B:J range depending on the A value
>
>
> TIA
>
>
> On Thu, 28 Jul 2005 11:50:08 +0100, "Roger Govier"
> <roger@technologyNOSPAM4u.co.uk> wrote:
>
>>Richard
>>It wasn't you causing confusion, it was me misreading.
>>In cell K1
>>=SUM(B1:OFFSET(B1,0,A1-1))
>>and copy down
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

Ron,

My apologies for confusing the matter. I should have referred to
column K rather than column C

Thanks for your input.

Rgds



On Thu, 28 Jul 2005 06:39:25 -0400, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:

>On Thu, 28 Jul 2005 10:13:36 +0100, Richard Buttrey
><chaos.theory.nospam.removethis@zen.co.uk> wrote:
>
>>Can anyone suggest a formula to do the following
>>
>>Where:
>>
>>A1 = 6
>>A2 = 3
>>A3 = 9
>>
>>B1:J1 = 1,2,3,4,5,6,7,8,9
>>B2:J2 = 1,2,3,4,5,6,7,8,9
>>B3:J3 = 1,2,3,4,5,6,7,8,9
>>
>>Formulae in C1:C3 to do the following:
>>
>>In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
>>range B1:J1 and result in the number 21
>>
>>In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
>>range B1:J1 and result in the number 6
>>
>>In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
>>range B1:J1 and result in the number 45
>>
>>Usual TIA
>>
>>
>>Regards
>>
>>
>>
>>__
>>Richard Buttrey
>
>It's not possible as the conditions you specify set up a circular reference; in
>addition, you cannot have both the number '2' and a formula in the same cell
>(column C).
>
>If you would care to purt your formulas in other than C1:C3, you could use the
>formula:
>
> =SUM(OFFSET(B1,,,,A1))
>
>for Row 1 and copy/drag down as needed.
>
>If you want something else, or really want to use a circular reference, I'd
>need more details of exactly what you are trying to accomplish.
>
>
>--ron

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 28 Jul 2005 13:07:22 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>Ron,
>
>My apologies for confusing the matter. I should have referred to
>column K rather than column C
>
>Thanks for your input.
>
>Rgds

Oops, I saw your subsequent posting and see how you want to determine 1, vs
1&4, vs 1&4&7.

Assuming you're being quite specific, the following formula should do what you
describe:

=SUMPRODUCT((COLUMN(B1:J1)<=CHOOSE(A1/3,2,5,8))*
(MOD(COLUMN(B1:J1)-2,3)=0)*B1:J1)


--ron

Can anyone suggest a formula to do the following

Where:

A1 = 6
A2 = 3
A3 = 9

B1:J1 = 1,2,3,4,5,6,7,8,9
B2:J2 = 1,2,3,4,5,6,7,8,9
B3:J3 = 1,2,3,4,5,6,7,8,9

Formulae in C1:C3 to do the following:

In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
range B1:J1 and result in the number 21

In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
range B1:J1 and result in the number 6

In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
range B1:J1 and result in the number 45

Usual TIA


Regards



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 28 Jul 2005 13:07:22 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>Ron,
>
>My apologies for confusing the matter. I should have referred to
>column K rather than column C
>
>Thanks for your input.
>
>Rgds

You're welcome.

K1: =SUM(OFFSET(B1,,,,A1))

should work just fine. Copy/Drag down as needed.

You also asked about, 1; 1&4; 1&4&7 depending on contents of A1.

I'm not certain of the relationship between A1 and your three choices.

Since 1-9 are already spoken for, I suppose you could use A, B, & C for your
three choices.

Or did you have something else in mind?


--ron

Richard
In L1
=CHOOSE(--SUM(A1>=7,A1>=4)+1,B1,(B1+E1),(B1+E1+H1))
copy down

--
Regards
Roger Govier
"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:pbihe1l293sn7r6koh3vlv45f4k5r8sm69@4ax.com...
> Roger,
>
> Brilliant. Thanks.
>
> Sorry to be a pest but do you have any thoughts on the second example,
> where I want to sum either the 1st, 1st & 4th or 1st, 4th & 7th
> columns in the B:J range depending on the A value
>
>
> TIA
>
>
> On Thu, 28 Jul 2005 11:50:08 +0100, "Roger Govier"
> <roger@technologyNOSPAM4u.co.uk> wrote:
>
>>Richard
>>It wasn't you causing confusion, it was me misreading.
>>In cell K1
>>=SUM(B1:OFFSET(B1,0,A1-1))
>>and copy down
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

Ron,

My apologies for confusing the matter. I should have referred to
column K rather than column C

Thanks for your input.

Rgds



On Thu, 28 Jul 2005 06:39:25 -0400, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:

>On Thu, 28 Jul 2005 10:13:36 +0100, Richard Buttrey
><chaos.theory.nospam.removethis@zen.co.uk> wrote:
>
>>Can anyone suggest a formula to do the following
>>
>>Where:
>>
>>A1 = 6
>>A2 = 3
>>A3 = 9
>>
>>B1:J1 = 1,2,3,4,5,6,7,8,9
>>B2:J2 = 1,2,3,4,5,6,7,8,9
>>B3:J3 = 1,2,3,4,5,6,7,8,9
>>
>>Formulae in C1:C3 to do the following:
>>
>>In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
>>range B1:J1 and result in the number 21
>>
>>In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
>>range B1:J1 and result in the number 6
>>
>>In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
>>range B1:J1 and result in the number 45
>>
>>Usual TIA
>>
>>
>>Regards
>>
>>
>>
>>__
>>Richard Buttrey
>
>It's not possible as the conditions you specify set up a circular reference; in
>addition, you cannot have both the number '2' and a formula in the same cell
>(column C).
>
>If you would care to purt your formulas in other than C1:C3, you could use the
>formula:
>
> =SUM(OFFSET(B1,,,,A1))
>
>for Row 1 and copy/drag down as needed.
>
>If you want something else, or really want to use a circular reference, I'd
>need more details of exactly what you are trying to accomplish.
>
>
>--ron

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Roger,

Brilliant. Thanks.

Sorry to be a pest but do you have any thoughts on the second example,
where I want to sum either the 1st, 1st & 4th or 1st, 4th & 7th
columns in the B:J range depending on the A value


TIA


On Thu, 28 Jul 2005 11:50:08 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>Richard
>It wasn't you causing confusion, it was me misreading.
>In cell K1
>=SUM(B1:OFFSET(B1,0,A1-1))
>and copy down

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard
It wasn't you causing confusion, it was me misreading.
In cell K1
=SUM(B1:OFFSET(B1,0,A1-1))
and copy down


--
Regards
Roger Govier
"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:ohahe15v07ierj1mech47gugdg8mavvt30@4ax.com...
> On Thu, 28 Jul 2005 10:20:24 +0100, "Roger Govier"
> <roger@technologyNOSPAM4u.co.uk> wrote:
>
>>In cell C1
>>=SUM(INDIRECT("B1:B"&A1)
>>copy down through C2:C3
>
>
> Roger,
>
> Thanks for the quick repsonse. I apologise for sewing some confusion.
>
> I should have said that I want the results in K1:K3 and that it is
> Cells B1, C1, D1, E1 etc, which have the values 1, 2, 3, 4.. etc
> respectively. i.e. I'm adding a variable range of either 3, 6 or 9
> cells depending on the value in A
>
> I also need an additional formula which adds the first, fourth or
> seventh cells depending on the value in A.
>
> i.e. given the earlier data.
>
> In say L1 (where A1= 6) I need to add B1 and E1 = 5
> In say L2 (where A1= 3) I just need B1 = 1
> In say L3 (where A1= 9) I need to add B1 and E1 and H1= 12
>
> Any ideas / suggestion gratefully received.
>
> Regards
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________

On Thu, 28 Jul 2005 10:13:36 +0100, Richard Buttrey
<chaos.theory.nospam.removethis@zen.co.uk> wrote:

>Can anyone suggest a formula to do the following
>
>Where:
>
>A1 = 6
>A2 = 3
>A3 = 9
>
>B1:J1 = 1,2,3,4,5,6,7,8,9
>B2:J2 = 1,2,3,4,5,6,7,8,9
>B3:J3 = 1,2,3,4,5,6,7,8,9
>
>Formulae in C1:C3 to do the following:
>
>In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
>range B1:J1 and result in the number 21
>
>In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
>range B1:J1 and result in the number 6
>
>In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
>range B1:J1 and result in the number 45
>
>Usual TIA
>
>
>Regards
>
>
>
>__
>Richard Buttrey

It's not possible as the conditions you specify set up a circular reference; in
addition, you cannot have both the number '2' and a formula in the same cell
(column C).

If you would care to purt your formulas in other than C1:C3, you could use the
formula:

=SUM(OFFSET(B1,,,,A1))

for Row 1 and copy/drag down as needed.

If you want something else, or really want to use a circular reference, I'd
need more details of exactly what you are trying to accomplish.


--ron

On Thu, 28 Jul 2005 10:20:24 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>In cell C1
>=SUM(INDIRECT("B1:B"&A1)
>copy down through C2:C3


Roger,

Thanks for the quick repsonse. I apologise for sewing some confusion.

I should have said that I want the results in K1:K3 and that it is
Cells B1, C1, D1, E1 etc, which have the values 1, 2, 3, 4.. etc
respectively. i.e. I'm adding a variable range of either 3, 6 or 9
cells depending on the value in A

I also need an additional formula which adds the first, fourth or
seventh cells depending on the value in A.

i.e. given the earlier data.

In say L1 (where A1= 6) I need to add B1 and E1 = 5
In say L2 (where A1= 3) I just need B1 = 1
In say L3 (where A1= 9) I need to add B1 and E1 and H1= 12

Any ideas / suggestion gratefully received.

Regards

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

In cell C1
=SUM(INDIRECT("B1:B"&A1)
copy down through C2:C3

--
Regards
Roger Govier
"Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:fu7he1hj9htbl1h5q2nnkiobrroe8e0n1a@4ax.com...
> Can anyone suggest a formula to do the following
>
> Where:
>
> A1 = 6
> A2 = 3
> A3 = 9
>
> B1:J1 = 1,2,3,4,5,6,7,8,9
> B2:J2 = 1,2,3,4,5,6,7,8,9
> B3:J3 = 1,2,3,4,5,6,7,8,9
>
> Formulae in C1:C3 to do the following:
>
> In C1 I want to add the first 6 (the 6 is defined by A1) cells in the
> range B1:J1 and result in the number 21
>
> In C2 I want to add the first 3 (the 3 is defined by A2) cells in the
> range B1:J1 and result in the number 6
>
> In C3 I want to add the first 9 (the 9 is defined by A3) cells in the
> range B1:J1 and result in the number 45
>
> Usual TIA
>
>
> Regards
>
>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________