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