Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM Nth Column in Array (Excel 97-SR2)

    Hi Folks

    OK.. this, as an array formula works for me:

    {=SUM((lstGroups=$C59)*lstColumn)}

    Where lstGroups is something like B1:B20 and lstColumn is a simlar sized single column array, e.g. Z21:Z40.

    What I want to do however is replace lstColumn with a table of results and operate on a single column of the table/array. e.g.

    tblResults = AA1:AZ20

    I sorta of think I ought to be able to do something like:

    {=SUM((lstGroups=$C59)*INDEX(tblResults,,n))} - Where 'n' is the column I want to work on.

    Which didn't work when I started typing this missive but now does... Hmm.. Err, is there a better way?

    Regards
    Peter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    INDEX(tblResults,0,n) will give you the nth column

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    Thanks Steve

    Added sugar and caffeine, brain AND function now working again.

    Regards
    Peter

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    I'd think that there is no need for an array-formula like

    {=SUM((lstGroups=$C59)*INDEX(tblResults,0,n))

    Use instead the ordinary....

    =SUMIF(lstGroups,$C59,INDEX(tblResults,0,n))

    Aladin
    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    Thank you, this does indeed work just as well. This is now my formula:-

    =SUMIF(lstPeopleGroups,$E6,IF(ReportPlanCosts,INDE X(tblPeoplePlanCosts,0,F$4),INDEX(tblPeopleActualC osts,0,F$4)))

    i.e. The user can select two report types, depends on value of ReportPlanCosts (True/False).

    Some supplementary questions.

    I have been asked to add further report types, I can just use nested IF's to pull the correct data through. Is there a neater solution?

    Are there pros/cons to using/not using array formulas? (Just aiding my education here..)

    Regards
    Peter

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    Assuming that F$4 just houses a column number...

    Create a 2-column list of report identifiers and relevant column numbers from tblPeopleActualCosts in a worksheet named, say, Admin, something like...

    Rep1 2
    Rep2 3
    etc.

    Name this 2-column list, e.g., RepList (using the Name Box).

    The above setup would allow you to use:

    =SUMIF(lstPeopleGroups,$E6,INDEX(tblPeopleActualCo sts,0,VLOOKUP(x,RepList,2,0)))

    where x is a cell which houses a report choice the user makes like Rep2. VLOOKUP will convert this to the relevant column number for the INDEX bit.

    Is this on the right track?
    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    Hi Aladin

    Nearly, but not quite. While F$4 is just a column number, the existing IF statement choses between two separate arrays, tblPeoplePlanCosts or tblPeopleActualCosts. i.e. Planned costs or Actual Costs.

    Your technique might/should work if I combined all of the separate tables into one large table and just worked with the array elements I need for the report selection.

    And while typing, I cracked it.....

    OK, What you do is define a Named Cell somewhere, called say 'ReportType'. In this cell you put the name of the array that contains your report data. Now you can change your formula to something like:

    =SUMIF(lstPeopleGroups,$E6,INDEX(INDIRECT(ReportTy pe),0,F$4))

    Which is more readable than the original:

    =SUMIF(lstPeopleGroups,$E6,IF(ReportPlanCosts,INDE X(tblPeoplePlanCosts,0,F$4),INDEX(tblPeopleActualC osts,0,F$4)))

    And can also be extended for as many report types as you like!

    Many thanks for your response and helping to stimulate my thought processes!

    Regards
    Peter

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    If you just have ReportPlanCosts and tblPeopleActualCosts as report types... use your old formula, here a bit modified...

    =SUMIF(lstPeopleGroups,$E6,INDEX(IF(ReportPlanCost s,tblPeoplePlanCosts,tblPeopleActualCosts),0,F$4))

    What I'm up to is, if possible, to eliminate INDIRECT which is a volatile function.

    Aladin
    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    OOoh, that's neat! Though I do have more than two report types now. I guess same principle you suggest would work with another INDEX, like so:

    =SUMIF(lstPeopleGroups,$E6,INDEX(INDEX(tblReportTy pes,TypeofReportSelected),0,F$4))

    Where 'tblReportTypes' is a table/array of pointers to the Named Ranges tblPeoplePlanCosts,tblPeopleActualCosts,tbl..... etc and 'TypeOfReportSelected' is an index?

    If you have time, can you enlighten me as to why INDIRECT is a volatile function, or point me at previous posts. (Just aiding my education here.) I use INDIRECT reasonably often without issue. Have I just been lucky to have not encountered problems?

    Regards
    Peter

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM Nth Column in Array (Excel 97-SR2)

    Peter,

    INDIRECT dereferences the references it is given in order to fetch the data in the actual range. I suppose that's why it is volatile. It is a fact that volatile functions prolonge recalc times. So it's nothing but a performance issue. Certainly not a ban on such functions. See

    http://www.decisionmodels.com/

    for performance problems.

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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