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

1. ## 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. ## Re: SUM Nth Column in Array (Excel 97-SR2)

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

Steve

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

Thanks Steve

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

Regards
Peter

4. ## 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))

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

5. ## 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. ## 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?

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

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. ## 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.

9. ## 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. ## 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.