Results 1 to 10 of 10

20021009, 13:39 #1
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
SUM Nth Column in Array (Excel 97SR2)
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

20021009, 14:24 #2
 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 97SR2)
INDEX(tblResults,0,n) will give you the nth column
Steve

20021009, 14:33 #3
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM Nth Column in Array (Excel 97SR2)
Thanks Steve
Added sugar and caffeine, brain AND function now working again.
Regards
Peter

20021009, 17:57 #4
 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 97SR2)
I'd think that there is no need for an arrayformula like
{=SUM((lstGroups=$C59)*INDEX(tblResults,0,n))
Use instead the ordinary....
=SUMIF(lstGroups,$C59,INDEX(tblResults,0,n))
AladinMicrosoft MVP  Excel

20021009, 18:57 #5
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM Nth Column in Array (Excel 97SR2)
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

20021009, 21:42 #6
 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 97SR2)
Assuming that F$4 just houses a column number...
Create a 2column 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 2column 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

20021010, 09:02 #7
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM Nth Column in Array (Excel 97SR2)
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

20021010, 14:17 #8
 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 97SR2)
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.
AladinMicrosoft MVP  Excel

20021010, 14:35 #9
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM Nth Column in Array (Excel 97SR2)
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

20021011, 03:41 #10
 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 97SR2)
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.
AladinMicrosoft MVP  Excel