Sumproduct or some such Array Solution

[Ian Mac]

I have the following formula:

=IF(SiteName<>"",IF(ProgrammeName<>"",IF(LINEMANAGER<>"",IF(EMPTYPE2<>"",SUMIF(RAWDATA!$N:$N,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$M:$M,$E8,RAWDATA!$G:$G)),IF(EMPTYPE2<>"",SUMIF(RAWDATA!$K:$K,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$J:$J,$E8,RAWDATA!$G:$G))),IF(LINEMANAGER<>"",IF(EMPTYPE2<>"",SUMIF(RAWDATA!$P:$P,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$O:$O,$E8,RAWDATA!$G:$G)),IF(EMPTYPE2<>"",SUMIF(RAWDATA!$L:$L,$E8,RAWDATA!$G:$G),SUMIF(RAWDATA!$I:$I,$E8,RAWDATA!$G:$G)))),SUMIF(RAWDATA!$B:$B,'EMS T&A REPORT'!$E8,RAWDATA!$G:$G))

Handed to me by someone else, I can go into more detail but on first inspection does any thing feel this would be easier acheived using an array.

what I can explain is:

the IF()'s are testing to see if there is any thing in the cells tested (fairly obivous).
What I'd like to know if you can test each say:

sumproduct(({SiteName,ProgrammeName,LINEMANAGER,EMPTYPE2}..............
and using TheAnswer with:

............indirect(Vlookup(TheAnswerSomethingIDon'tKnow?,{Answer1(all blank),"RawData!N:N";Answer2(AllBlankExceptSiteName),"RawData!M:M" Etc..........

With ............*(RawData!G:G).

one thing is I will be changing the N:N Etc. to dynamic named ranges (Can I do this).


Also, I tried this for just one of the IF()'s:

=SUMPRODUCT((RAWDATA!$N$2:$N$4671=E8)*(RAWDATA!$G$2:$G$4671))

but it doesn't work , what I will point is that E8 is a Text String.

I don't expect any answers now, as I don't fully understand it myself, but if anyone has any starters for 10 I'd be very great full, it's late 22:40 UK and I've been working since 08:30.



_________________
Kind Regards
Ian Mac

[ This Message was edited by: Ian Mac on 2002-03-05 14:22 ]

[Aladin Akyurek]

Ian,

Wouldn't be better to provide some sample data along with what one wants to do with it?

Aladin

[Ian Mac]

On 2002-03-05 14:36, Aladin Akyurek wrote:
Ian,

Wouldn't be better to provide some sample data along with what one wants to do with it?

Aladin

I'll try...Again. I've delete my other sample and will go iwth this.

On further examination of the data I have this is an example of what I'd like.

This is a small sample table A1:E11:

{"Site","Programme","Line Manager","Employee Type","Ammount";"Newcastle","Dummy Co","Adam","Perm",10;"Kingston","Made Up Ltd","Adam","Temp",10;"Newcastle","Dummy Co","Paul","Agency",10;"Newcastle","Made Up Ltd","Paul","Perm",10;"Kingston","Made Up Ltd","Paul","Perm",10;"Newcastle","False Inc","Adam","Perm",10;"Kingston","Dummy Co","Adam","Temp",10;"Kingston","Dummy Co","Adam","Agency",10;"Kingston","False Inc","Paul","Agency",10;"Newcastle","Dummy Co","Adam","Perm",10}

I have four drop down boxes in A17:A20

What I'd like is to select Something or nothing in the drop down boxes and get a result from Column E.

if I use:

=SUMPRODUCT((A2:A11=A17)*(B2:B11=A18)*(C2:C11=A19)*(D2:D11=A20),(E2:E11))

And have

{"Newcastle";"Dummy Co";"Adam";"Perm"}

as the criteria I get the return value of 20.

What I need is a formula that work even if 1 of the cells has nothing in it will egnor that as a criteria, so if I had:

{"Newcastle";0;"Adam";"Perm"}

I'd like the return value to be 30, i.e. a formula that will egnor the '0' and move to the next criteria and not return 0 because MY sumproduct looks for an empty cell in col B, which doesn't exist.

I hope this is clearer.

_________________
Kind Regards
Ian Mac

[ This Message was edited by: Ian Mac on 2002-03-07 03:57 ]

[ This Message was edited by: Ian Mac on 2002-03-07 03:58 ]

[Ian Mac]

Re-worked E.g.

[Aladin Akyurek]

On 2002-03-07 03:59, Ian Mac wrote:
Re-worked E.g.

Ian,

The problem is darn interesting.

Try:

=SUMPRODUCT((A2:A11=IF(LEN(A17)>1,A17,A2:A11))*(B2:B11=IF(LEN(A18)>1,A18,B2:B11))*(C2:C11=IF(LEN(A19)>1,A19,C2:C11))*(D2:D11=IF(LEN(A20)>1,A20,D2:D11)),(E2:E11))

Aladin

[Ian Mac]

Aladin, Top stuff!

Seems to be work fine I'm just about to test it in the real environment.

Do you have any opinions re: Pivot Table vs Indirect in terms of functionality? by that I mean if I use Indirect function (or another solution) to populate the drop down boxes, will this help for file size/speed?? Your thoughts are welcome.
Basically if it's not worth it I won't bother trying it.

Many Many Thanks,

[Aladin Akyurek]

On 2002-03-07 08:17, Ian Mac wrote:
Aladin, Top stuff!

Seems to be work fine I'm just about to test it in the real environment.

Do you have any opinions re: Pivot Table vs Indirect in terms of functionality? by that I mean if I use Indirect function (or another solution) to populate the drop down boxes, will this help for file size/speed?? Your thoughts are welcome.
Basically if it's not worth it I won't bother trying it.

Many Many Thanks,

Ian,

Not sure I understood fully last question.

PivotTables (PT) would also enable you to summarize the data at hand, so would constitute an alternative approach to a formula-based approach. Give it a try: maybe you don't need to reorganize the data too much. PT would be better than the SUMPRODUCT formulas that fill up hundreths of cells.

Aladin

[Ian Mac]

Sorry Aladin,

I didn't explain my self properly, the pivot table that is currently being used is looking at ALL 74000 odd cells and it's only function is when you select say Newcastle from the SITE it will filter only the Newcastle PROGRAMMES and TM - PROGAMME etc. I feel this might be better down with a few formulas, I'm going to give it a go anyway but thought I'd explain further.

BUT, I do have a problem, I've used the formula, you suggested with dynamic named ranges:

=SUMPRODUCT((ReportCat=B53)*(RawSite=IF(LEN(Site)>1,Site,RawSite))*(RawProg=IF(LEN(ProgrammeName)>1,ProgrammeName,RawProg))*(RawManager=IF(LEN(LINEMANAGER)>1,LINEMANAGER,RawManager))*(RawEmp=IF(LEN(EMPTYPE2)>1,EMPTYPE2,RawEmp)),RawHours)

this works fine except it is actually much slower than the original IF() thingy, I can only assume that's because it contains four IF's of it's own. The answer might be to go back to the old formula, the thing is your formula has far more Permutations as it cover the fact that SITE doesn't need to be selected to get a return value.

so I thought I could put the IF's outside of the formula so the only need to calculate once, I've tried this:

=SUMPRODUCT((ReportCat=B53)*(RawSite=INDIRECT($C$87))*(RawProg=INDIRECT($C$88))*(RawManager=INDIRECT($C$89))*(RawEmp=IND IRECT($C$90)),RawHours)

C87:C90 contains the IF and are returning text the apropriate text strings i.e RawEmp or EMPTYPE2 etc. It returns a #REF! error, what am I doing wrong?

[Aladin Akyurek]

On 2002-03-08 01:33, Ian Mac wrote:
Sorry Aladin,

I didn't explain my self properly, the pivot table that is currently being used is looking at ALL 74000 odd cells and it's only function is when you select say Newcastle from the SITE it will filter only the Newcastle PROGRAMMES and TM - PROGAMME etc. I feel this might be better down with a few formulas, I'm going to give it a go anyway but thought I'd explain further.

BUT, I do have a problem, I've used the formula, you suggested with dynamic named ranges:

=SUMPRODUCT((ReportCat=B53)*(RawSite=IF(LEN(Site)>1,Site,RawSite))*(RawProg=IF(LEN(ProgrammeName)>1,ProgrammeName,RawProg))*(RawManager=IF(LEN(LINEMANAGER)>1,LINEMANAGER,RawManager))*(RawEmp=IF(LEN(EMPTYPE2)>1,EMPTYPE2,RawEmp)),RawHours)

this works fine except it is actually much slower than the original IF() thingy, I can only assume that's because it contains four IF's of it's own. The answer might be to go back to the old formula, the thing is your formula has far more Permutations as it cover the fact that SITE doesn't need to be selected to get a return value.

so I thought I could put the IF's outside of the formula so the only need to calculate once, I've tried this:

=SUMPRODUCT((ReportCat=B53)*(RawSite=INDIRECT($C$87))*(RawProg=INDIRECT($C$88))*(RawManager=INDIRECT($C$89))*(RawEmp=IND IRECT($C$90)),RawHours)

C87:C90 contains the IF and are returning text the apropriate text strings i.e RawEmp or EMPTYPE2 etc. It returns a #REF! error, what am I doing wrong?

As you know, the original formula returns either a text value as condition or the range itself. (When the range itself is returned (we get something like A1:A10=A1:A10, which is simply a constant array of 1's that does not disturb the required computations). I'm not sure INDIRECT would function properly to return the values of the tested range.

Named dynamic ranges can also slow a spreadsheet when done via insert|name|define. You can also have dynamically evaluated ranges directly in the formulas themselves, allowing you to keep them in the original form.

Aladin

[Ian Mac]

Aladin,

Your quite right INDIRECT() is not working with the SUMPRODUCT(), I was merely tryint to avoid having to run all the IF statements within every formula, I'm assuming there's no other way.

not entirely sure what you mean by 'Original Form', but I have amended the formula to:

=SUMPRODUCT((OFFSET(RAWDATA!$B$1,0,0,COUNTA(RAWDATA!$B:$B),1)=$B8)*(OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)=IF( LEN(Site)>1,Site,OFFSET(RAWDATA!$C$1,0,0,COUNTA(RAWDATA!$C:$C),1)))*(OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)=IF(LEN(Progr ammeName)>1,ProgrammeName,OFFSET(RAWDATA!$D$1,0,0,COUNTA(RAWDATA!$D:$D),1)))*(OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)=IF( LEN(LINEMANAGER)>1,LINEMANAGER,OFFSET(RAWDATA!$E$1,0,0,COUNTA(RAWDATA!$E:$E),1)))*(OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1)=IF(LE N(EMPTYPE2)>1,EMPTYPE2,OFFSET(RAWDATA!$F$1,0,0,COUNTA(RAWDATA!$F:$F),1))),OFFSET(RAWDATA!$H$1,0,0,COUNTA(RAWDATA!$H:$H),1))

which is what I think you mean.

it is running a small ammount faster but not significantly enough to warrant the changes to the Original, BUT the fact that it replaces 16 IF's (or conditions rather) as opposed to the 9 conditions orginally, it does (nearly). Also the fact that the file is some 37368 cells lighter makes the the time concerns ALMOST worth it.