+ Reply to Thread
Results 1 to 10 of 10

Summing a variable range of columns

  1. #1
    Richard Buttrey
    Guest

    Summing a variable range of columns

    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
    __________________________

  2. #2
    Roger Govier
    Guest

    Re: 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
    > __________________________




  3. #3
    Richard Buttrey
    Guest

    Re: Summing a variable range of columns

    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
    __________________________

  4. #4
    Ron Rosenfeld
    Guest

    Re: Summing a variable range of columns

    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

  5. #5
    Roger Govier
    Guest

    Re: Summing a variable range of columns

    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
    > __________________________




  6. #6
    Richard Buttrey
    Guest

    Re: Summing a variable range of columns

    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
    __________________________

  7. #7
    Richard Buttrey
    Guest

    Re: Summing a variable range of columns

    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
    __________________________

  8. #8
    Roger Govier
    Guest

    Re: Summing a variable range of columns

    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
    > __________________________




  9. #9
    Ron Rosenfeld
    Guest

    Re: Summing a variable range of columns

    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

  10. #10
    Ron Rosenfeld
    Guest

    Re: Summing a variable range of columns

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1