Sumproduct or some such Array Solution
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Sumproduct or some such Array Solution

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    Ian,

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

    Aladin

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Re-worked E.g.
    "Have a good time......all the time"
    Ian Mac

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,
    "Have a good time......all the time"
    Ian Mac

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


    "Have a good time......all the time"
    Ian Mac

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.
    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com