|
Formula Question
I have the following formula on one worksheet 240 times. Of course the
formula is a bit different but the length is pretty much the same. Is there an easier way to speed up the calculating process of this worksheet? Can this type of formual be put in VB code behind the worksheet and will that make it compute faster? Not really sure if any of this is possible but I figured I'd ask. Just looking to speed up the calculations. =IF($B$3="All",IF($I$3="W/ BLANKET ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$B$2:$B$49706<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706<"All"))),IF($I$3="W/ BLANKET ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))) |
Formula Question
I noticed that there is a lot of redundancy in your Sumproduct agruments. Try
this experimentally in place of your formula. It's a bit of a wild shot since I can't test it. Commit with Ctrl + Shift + Enter: =SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEA R(Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N $49706=1)*(Detail!$Q$2:$Q$49706=0)*IF($B$3="All", (Detail!$B$2:$B$49706<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$49706="F"),(Detail!$B$2:$ B$49706=LeadTimes!$B$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$49706="F"))) Greg "Secret Squirrel" wrote: I have the following formula on one worksheet 240 times. Of course the formula is a bit different but the length is pretty much the same. Is there an easier way to speed up the calculating process of this worksheet? Can this type of formual be put in VB code behind the worksheet and will that make it compute faster? Not really sure if any of this is possible but I figured I'd ask. Just looking to speed up the calculations. =IF($B$3="All",IF($I$3="W/ BLANKET ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$B$2:$B$49706<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706<"All"))),IF($I$3="W/ BLANKET ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))) |
Formula Question
Thanks for your help Greg. It works fine that way. Question though....if I go
ahead and change all my formulas to this way will it speed up the calculations since the formula is now shorter? "Greg Wilson" wrote: I noticed that there is a lot of redundancy in your Sumproduct agruments. Try this experimentally in place of your formula. It's a bit of a wild shot since I can't test it. Commit with Ctrl + Shift + Enter: =SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEA R(Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N $49706=1)*(Detail!$Q$2:$Q$49706=0)*IF($B$3="All", (Detail!$B$2:$B$49706<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$49706="F"),(Detail!$B$2:$ B$49706=LeadTimes!$B$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$49706="F"))) Greg "Secret Squirrel" wrote: I have the following formula on one worksheet 240 times. Of course the formula is a bit different but the length is pretty much the same. Is there an easier way to speed up the calculating process of this worksheet? Can this type of formual be put in VB code behind the worksheet and will that make it compute faster? Not really sure if any of this is possible but I figured I'd ask. Just looking to speed up the calculations. =IF($B$3="All",IF($I$3="W/ BLANKET ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$B$2:$B$49706<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706<"All"))),IF($I$3="W/ BLANKET ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))) |
Formula Question
I'm not a worksheet formula expert. So test my formula rigorously. I only
gave it a cursory test under highly simplified conditions. To compare the two formulae: 1. First make a copy of your wb. 2. In the new wb, insert a copy of your formula. 3. Drag the formula down so that it autofills hundreds of rows. 4. Change the value of a cell referenced by the formula so that it must recalculate and see how it performs. 5. Clear the range of formulae created in step 3 and repeat steps 2 to 4 using my formula instead. FYI, your formula is in the form of: =IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) whe SP1() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(EEE)) SP2() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(EEE)) SP3() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(GGG)) SP4() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(GGG)) whe (AAA), (BBB) etc. are abbreviations for the Sumproduct arguments. You have only 7 different Sumproduct arguments, (AAA) to (GGG). But these arguments are listed 22 times in the formula *and therefore evaluated 22 times* because of the repetition. My formula is an array formula (Ctrl + Shift + Enter) in the form of: =SUM((AAA)*(BBB)*(CCC)*(DDD)*IF(Cond1, (EEE)*IF(Cond2,1,(FFF)),(GGG)*IF(Cond2,1,(FFF)))) You can see that only argument (FFF) is evaluated more than once. Greg |
Formula Question
I understand now. Thanks for the explanation. I went through and changed all
my formulas to mirror yours and it does calculate much faster. I tested it based on the time it took mine to calculate. It went from 20 seconds down to 3 seconds. That's a very drastic change in calculation time. I have just one follow up question. I need to add another piece to this formula now. Where I was calling out cell B3 I now need to do the same for cell J3. How would I add that to the formula you wrote? =SUM((MONTH(Detail!$J$2:$J$12000)=MONTH(B15))*(YEA R(Detail!$J$2:$J$12000)=YEAR(B15))*(Detail!$N$2:$N $12000=1)*(Detail!$Q$2:$Q$12000=0)*IF($B$3="All", (Detail!$B$2:$B$12000<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"),(Detail!$B$2:$ B$12000=LeadTimes!$B$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"))) I appreciate all your help here. "Greg Wilson" wrote: I'm not a worksheet formula expert. So test my formula rigorously. I only gave it a cursory test under highly simplified conditions. To compare the two formulae: 1. First make a copy of your wb. 2. In the new wb, insert a copy of your formula. 3. Drag the formula down so that it autofills hundreds of rows. 4. Change the value of a cell referenced by the formula so that it must recalculate and see how it performs. 5. Clear the range of formulae created in step 3 and repeat steps 2 to 4 using my formula instead. FYI, your formula is in the form of: =IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) whe SP1() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(EEE)) SP2() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(EEE)) SP3() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(GGG)) SP4() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(GGG)) whe (AAA), (BBB) etc. are abbreviations for the Sumproduct arguments. You have only 7 different Sumproduct arguments, (AAA) to (GGG). But these arguments are listed 22 times in the formula *and therefore evaluated 22 times* because of the repetition. My formula is an array formula (Ctrl + Shift + Enter) in the form of: =SUM((AAA)*(BBB)*(CCC)*(DDD)*IF(Cond1, (EEE)*IF(Cond2,1,(FFF)),(GGG)*IF(Cond2,1,(FFF)))) You can see that only argument (FFF) is evaluated more than once. Greg |
Formula Question
The formula structure is here repeated. Cell B3 is referenced in Cond1:
=SUM((AAA)*(BBB)*(CCC)*(DDD)*IF(Cond1,(EEE)*IF(Con d2,1,(FFF)),(GGG)*IF(Cond2,1,(FFF)))) It depends on what you mean by "do the same for J3". I assume you don't mean just change the reference from B3 to J3 because that's too simple. I assume you want to change the structure to include another condition, i.e. where J3 = whatever. You need to specify what you want to happen when the new condition is True/False. Greg "Secret Squirrel" wrote: I understand now. Thanks for the explanation. I went through and changed all my formulas to mirror yours and it does calculate much faster. I tested it based on the time it took mine to calculate. It went from 20 seconds down to 3 seconds. That's a very drastic change in calculation time. I have just one follow up question. I need to add another piece to this formula now. Where I was calling out cell B3 I now need to do the same for cell J3. How would I add that to the formula you wrote? =SUM((MONTH(Detail!$J$2:$J$12000)=MONTH(B15))*(YEA R(Detail!$J$2:$J$12000)=YEAR(B15))*(Detail!$N$2:$N $12000=1)*(Detail!$Q$2:$Q$12000=0)*IF($B$3="All", (Detail!$B$2:$B$12000<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"),(Detail!$B$2:$ B$12000=LeadTimes!$B$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"))) I appreciate all your help here. "Greg Wilson" wrote: I'm not a worksheet formula expert. So test my formula rigorously. I only gave it a cursory test under highly simplified conditions. To compare the two formulae: 1. First make a copy of your wb. 2. In the new wb, insert a copy of your formula. 3. Drag the formula down so that it autofills hundreds of rows. 4. Change the value of a cell referenced by the formula so that it must recalculate and see how it performs. 5. Clear the range of formulae created in step 3 and repeat steps 2 to 4 using my formula instead. FYI, your formula is in the form of: =IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) whe SP1() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(EEE)) SP2() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(EEE)) SP3() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(GGG)) SP4() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(GGG)) whe (AAA), (BBB) etc. are abbreviations for the Sumproduct arguments. You have only 7 different Sumproduct arguments, (AAA) to (GGG). But these arguments are listed 22 times in the formula *and therefore evaluated 22 times* because of the repetition. My formula is an array formula (Ctrl + Shift + Enter) in the form of: =SUM((AAA)*(BBB)*(CCC)*(DDD)*IF(Cond1, (EEE)*IF(Cond2,1,(FFF)),(GGG)*IF(Cond2,1,(FFF)))) You can see that only argument (FFF) is evaluated more than once. Greg |
Formula Question
Your assumption is correct. I want to add another condition just like the one
in B3. If(J3= "All",(Detail!$T$2:$T$12000<"All") (Detail!$T$2:$T$12000=LeadTimes!$J$3) Above are the same conditions I have for B3 so I want to duplicate them as another condition. Where would I add these parts to the formula? It's just another way for me to filter my data when selections are made in either B3, J3, and I3. "Greg Wilson" wrote: The formula structure is here repeated. Cell B3 is referenced in Cond1: =SUM((AAA)*(BBB)*(CCC)*(DDD)*IF(Cond1,(EEE)*IF(Con d2,1,(FFF)),(GGG)*IF(Cond2,1,(FFF)))) It depends on what you mean by "do the same for J3". I assume you don't mean just change the reference from B3 to J3 because that's too simple. I assume you want to change the structure to include another condition, i.e. where J3 = whatever. You need to specify what you want to happen when the new condition is True/False. Greg "Secret Squirrel" wrote: I understand now. Thanks for the explanation. I went through and changed all my formulas to mirror yours and it does calculate much faster. I tested it based on the time it took mine to calculate. It went from 20 seconds down to 3 seconds. That's a very drastic change in calculation time. I have just one follow up question. I need to add another piece to this formula now. Where I was calling out cell B3 I now need to do the same for cell J3. How would I add that to the formula you wrote? =SUM((MONTH(Detail!$J$2:$J$12000)=MONTH(B15))*(YEA R(Detail!$J$2:$J$12000)=YEAR(B15))*(Detail!$N$2:$N $12000=1)*(Detail!$Q$2:$Q$12000=0)*IF($B$3="All", (Detail!$B$2:$B$12000<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"),(Detail!$B$2:$ B$12000=LeadTimes!$B$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"))) I appreciate all your help here. "Greg Wilson" wrote: I'm not a worksheet formula expert. So test my formula rigorously. I only gave it a cursory test under highly simplified conditions. To compare the two formulae: 1. First make a copy of your wb. 2. In the new wb, insert a copy of your formula. 3. Drag the formula down so that it autofills hundreds of rows. 4. Change the value of a cell referenced by the formula so that it must recalculate and see how it performs. 5. Clear the range of formulae created in step 3 and repeat steps 2 to 4 using my formula instead. FYI, your formula is in the form of: =IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) whe SP1() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(EEE)) SP2() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(EEE)) SP3() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(GGG)) SP4() = Sumproduct(--(AAA),--(BBB),--(CCC),--(DDD),--(FFF),--(GGG)) whe (AAA), (BBB) etc. are abbreviations for the Sumproduct arguments. You have only 7 different Sumproduct arguments, (AAA) to (GGG). But these arguments are listed 22 times in the formula *and therefore evaluated 22 times* because of the repetition. My formula is an array formula (Ctrl + Shift + Enter) in the form of: =SUM((AAA)*(BBB)*(CCC)*(DDD)*IF(Cond1, (EEE)*IF(Cond2,1,(FFF)),(GGG)*IF(Cond2,1,(FFF)))) You can see that only argument (FFF) is evaluated more than once. Greg |
Formula Question
Looking at your original formula which is more intuitive:
=IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) Translation: If Cond1 = True and Cond2 = True then evaluate SP1() If Cond1 = True and Cond2 = False then evaluate SP2() If Cond1 = False and Cond2 = True then evaluate SP3() If Cond1 = False and Cond2 = False then evaluate SP4() The IF function referencing Cond1 has two branches. For each branch, the IF function referencing Cond2 has two branches. Total: four Sumproduct options. I'm still confused as to how you want to include the new condition (call it Cond3). Is Cond2 child to Cond3 or vice versa or neither? 1. Please specify the logic structure as per the format I used above for your original formula. 2. Call the new condition Cond3. 3. Call new Sumproduct options SP5() and SP6(). 4. Specify the arguments of SP5() and SP6(). You can use abbreviations (AAA) to (DDD). Beyond that you should probably spell them out to avoid confusion. If you intend others to use your project, 20 seconds response time is basically dysfunctional while 3 seconds is klunky. Adding another condition will make it worse. Are you sure you want to do this? Depending on how your worksheet is structured, one thought is that it may be feasible to have a subset (say a table of interest) update at a time, where the user clicks a "Calculate" button. The actual performance won't be improved since the user has to respond, but the optics may be improved. This can be managed with VBA. Be advised that I have to go out for several hours and can't check your response until this evening. Greg |
Formula Question
I tried to walk through this as easily as possible using my old formula. I
tried spelling out all the conditions so we're both on the same page. I understand what you're saying and I used my original formula to walk through each condition. See Below: If B3 = All And I3 = W/ Blanket Orders And M3 = All is True the return all rows If B3<All And I3 = W/ Blanket Orders And M3 = All is True then return all rows where B3 equals Column B on Details tab If B3=All And I3 = W/ Blanket Orders And M3<All is True then return all rows where M3 equals Column M on Details tab If B3<All And I3 = W/ Blanket Orders And M3<All is True then return all rows where B3 equals Column B on Details tab And then return all rows where M3 equals Column M on Details tab Example: If B3 = My Company then give me all rows where My Company is in Column B and then find within the My Company rows any rows that M3 equals column M If B3=All And I3< W/ Blanket Orders And M3=All is True then return all rows where Column L equals F If B3<All And I3< W/ Blanket Orders And M3=All is True then return all rows where B3 equals Column B on Details tab And where Column L equals F If B3=All And I3< W/ Blanket Orders And M3<All is True then return all rows where Column L equals F on Details tab And where M3 equals Column M on Details tab If B3=<All And I3< W/ Blanket Orders And M3<All is True then return all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab The above are all using the 3rd condition. I think I covered them all. Review it when you have a chance and let me know what you think. If it makes it easier for you I can send you my file so you can see what I'm trying to do. Thanks Greg! "Greg Wilson" wrote: Looking at your original formula which is more intuitive: =IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) Translation: If Cond1 = True and Cond2 = True then evaluate SP1() If Cond1 = True and Cond2 = False then evaluate SP2() If Cond1 = False and Cond2 = True then evaluate SP3() If Cond1 = False and Cond2 = False then evaluate SP4() The IF function referencing Cond1 has two branches. For each branch, the IF function referencing Cond2 has two branches. Total: four Sumproduct options. I'm still confused as to how you want to include the new condition (call it Cond3). Is Cond2 child to Cond3 or vice versa or neither? 1. Please specify the logic structure as per the format I used above for your original formula. 2. Call the new condition Cond3. 3. Call new Sumproduct options SP5() and SP6(). 4. Specify the arguments of SP5() and SP6(). You can use abbreviations (AAA) to (DDD). Beyond that you should probably spell them out to avoid confusion. If you intend others to use your project, 20 seconds response time is basically dysfunctional while 3 seconds is klunky. Adding another condition will make it worse. Are you sure you want to do this? Depending on how your worksheet is structured, one thought is that it may be feasible to have a subset (say a table of interest) update at a time, where the user clicks a "Calculate" button. The actual performance won't be improved since the user has to respond, but the optics may be improved. This can be managed with VBA. Be advised that I have to go out for several hours and can't check your response until this evening. Greg |
Formula Question
Hi Greg,
After playing with this formula for a bit I have most of it figured out with the third condition in it. The only problem is when B3 does not equal "All" and I3 equals "W/ Blanket Orders" and M3 equals "All", I don't get the correct result. I must be missing one part of the formula. Any chance you know where I went wrong? =SUM((MONTH(Detail!$J$2:$J$12000)=MONTH(B15))*(YEA R(Detail!$J$2:$J$12000)=YEAR(B15))*(Detail!$N$2:$N $12000=1)*(Detail!$Q$2:$Q$12000=0)*IF($M$3="All", (Detail!$M$2:$M$12000<"All")*(Detail!$B$2:$B$1200 0<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"),(Detail!$M$2:$ M$12000=LeadTimes!$M$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F")*IF($B$3="All", (Detail!$B$2:$B$12000<"All")*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F"),(Detail!$B$2:$ B$12000=LeadTimes!$B$3)*IF($I$3="W/ BLANKET ORDERS",1,Detail!$L$2:$L$12000="F")))) "Greg Wilson" wrote: Looking at your original formula which is more intuitive: =IF(Cond1, IF(Cond2, SP1(), SP2()), IF(Cond2, SP3(), SP4())) Translation: If Cond1 = True and Cond2 = True then evaluate SP1() If Cond1 = True and Cond2 = False then evaluate SP2() If Cond1 = False and Cond2 = True then evaluate SP3() If Cond1 = False and Cond2 = False then evaluate SP4() The IF function referencing Cond1 has two branches. For each branch, the IF function referencing Cond2 has two branches. Total: four Sumproduct options. I'm still confused as to how you want to include the new condition (call it Cond3). Is Cond2 child to Cond3 or vice versa or neither? 1. Please specify the logic structure as per the format I used above for your original formula. 2. Call the new condition Cond3. 3. Call new Sumproduct options SP5() and SP6(). 4. Specify the arguments of SP5() and SP6(). You can use abbreviations (AAA) to (DDD). Beyond that you should probably spell them out to avoid confusion. If you intend others to use your project, 20 seconds response time is basically dysfunctional while 3 seconds is klunky. Adding another condition will make it worse. Are you sure you want to do this? Depending on how your worksheet is structured, one thought is that it may be feasible to have a subset (say a table of interest) update at a time, where the user clicks a "Calculate" button. The actual performance won't be improved since the user has to respond, but the optics may be improved. This can be managed with VBA. Be advised that I have to go out for several hours and can't check your response until this evening. Greg |
Formula Question
Summary of Previous Post:
As per my formula, the idea is to extract all arguments that are common to each of the Sumproduct functions and list them only once as arguments in the Sum function (array version). Also included in the Sum function is a nested IF structure that returns the correct conditioin-specific argument from a list of options. In my previous post, I refered to the repeating arguments as (AAA) to (DDD) and the condition-specific arguments as (EEE) to (GGG). Current Strategy: We need to confirm which arguments repeat for all conditions and which arguments are condition-specific. As per my first formula, we will list the arguments that repeat in the Sum function and multiply them together with an IF structure that returns the correct condition-specific argument. Current Logic Structu The logic you describe is captured in the following formula: =IF(Cond1, IF(Cond2, IF(Cond3, SP1, SP2), IF(Cond3, SP3, SP4)), IF(Cond2, IF(Cond3, SP5, SP6), IF(Cond3, SP7, SP8))) Translation: If Cond1 and Cond2 and Cond3 Then SP1 If Cond1 and Cond2 and Not Cond3 Then SP2 If Cond1 and Not Cond2 and Cond3 Then SP3 If Cond1 and Not Cond2 and Not Cond3 Then SP4 If Not Cond1 and Cond2 and Cond3 Then SP5 If Not Cond1 and Cond2 and Not Cond3 Then SP6 If Not Cond1 and Not Cond2 and Cond3 Then SP7 If Not Cond1 and Not Cond2 and Not Cond3 Then SP8 Whe SP1 is a Sumproduct function that returns all rows SP2 returns all rows where M3 equals Column M on Details tab SP3 returns all rows where Column L equals F SP4 returns all rows where Column L equals F on Details tab And where M3 equals Column M on Details tab SP5 returns all rows where B3 equals Column B on Details tab SP6 returns all rows where B3 equals Column B on Details tab And then return all rows where M3 equals Column M on Details tab SP7 returns all rows where B3 equals Column B on Details tab And where Column L equals F SP8 returns all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab *** Required from You: 1. To obtain the above results, please confirm the arguments that repeat for each of SP1 to SP8. I assume these are still what I refered to as (AAA) to (DDD) in my previous post. 2. List for each (SP1 to SP8) the condition-specific arguments that (when multiplied by the repeating arguments) will return the desired results. *** My Assignment: Devise the formula. In other words, devise an IF structure that returns the correct condition-specific argument from the different options and multiply it together with the repeating arguments inside the Sum function. Greg |
Formula Question
I will work on that in the morning and get back to you.
Thanks Greg! "Greg Wilson" wrote: Summary of Previous Post: As per my formula, the idea is to extract all arguments that are common to each of the Sumproduct functions and list them only once as arguments in the Sum function (array version). Also included in the Sum function is a nested IF structure that returns the correct conditioin-specific argument from a list of options. In my previous post, I refered to the repeating arguments as (AAA) to (DDD) and the condition-specific arguments as (EEE) to (GGG). Current Strategy: We need to confirm which arguments repeat for all conditions and which arguments are condition-specific. As per my first formula, we will list the arguments that repeat in the Sum function and multiply them together with an IF structure that returns the correct condition-specific argument. Current Logic Structu The logic you describe is captured in the following formula: =IF(Cond1, IF(Cond2, IF(Cond3, SP1, SP2), IF(Cond3, SP3, SP4)), IF(Cond2, IF(Cond3, SP5, SP6), IF(Cond3, SP7, SP8))) Translation: If Cond1 and Cond2 and Cond3 Then SP1 If Cond1 and Cond2 and Not Cond3 Then SP2 If Cond1 and Not Cond2 and Cond3 Then SP3 If Cond1 and Not Cond2 and Not Cond3 Then SP4 If Not Cond1 and Cond2 and Cond3 Then SP5 If Not Cond1 and Cond2 and Not Cond3 Then SP6 If Not Cond1 and Not Cond2 and Cond3 Then SP7 If Not Cond1 and Not Cond2 and Not Cond3 Then SP8 Whe SP1 is a Sumproduct function that returns all rows SP2 returns all rows where M3 equals Column M on Details tab SP3 returns all rows where Column L equals F SP4 returns all rows where Column L equals F on Details tab And where M3 equals Column M on Details tab SP5 returns all rows where B3 equals Column B on Details tab SP6 returns all rows where B3 equals Column B on Details tab And then return all rows where M3 equals Column M on Details tab SP7 returns all rows where B3 equals Column B on Details tab And where Column L equals F SP8 returns all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab *** Required from You: 1. To obtain the above results, please confirm the arguments that repeat for each of SP1 to SP8. I assume these are still what I refered to as (AAA) to (DDD) in my previous post. 2. List for each (SP1 to SP8) the condition-specific arguments that (when multiplied by the repeating arguments) will return the desired results. *** My Assignment: Devise the formula. In other words, devise an IF structure that returns the correct condition-specific argument from the different options and multiply it together with the repeating arguments inside the Sum function. Greg |
Formula Question
Hi Greg,
I reviewed your last post and all the SP functions you have are correct. I'm a little unclear how you want me to list each (SP1 to SP8) and the condition-specific arguments. Isn't that what you have under the "Where" section of your last post? "Greg Wilson" wrote: Summary of Previous Post: As per my formula, the idea is to extract all arguments that are common to each of the Sumproduct functions and list them only once as arguments in the Sum function (array version). Also included in the Sum function is a nested IF structure that returns the correct conditioin-specific argument from a list of options. In my previous post, I refered to the repeating arguments as (AAA) to (DDD) and the condition-specific arguments as (EEE) to (GGG). Current Strategy: We need to confirm which arguments repeat for all conditions and which arguments are condition-specific. As per my first formula, we will list the arguments that repeat in the Sum function and multiply them together with an IF structure that returns the correct condition-specific argument. Current Logic Structu The logic you describe is captured in the following formula: =IF(Cond1, IF(Cond2, IF(Cond3, SP1, SP2), IF(Cond3, SP3, SP4)), IF(Cond2, IF(Cond3, SP5, SP6), IF(Cond3, SP7, SP8))) Translation: If Cond1 and Cond2 and Cond3 Then SP1 If Cond1 and Cond2 and Not Cond3 Then SP2 If Cond1 and Not Cond2 and Cond3 Then SP3 If Cond1 and Not Cond2 and Not Cond3 Then SP4 If Not Cond1 and Cond2 and Cond3 Then SP5 If Not Cond1 and Cond2 and Not Cond3 Then SP6 If Not Cond1 and Not Cond2 and Cond3 Then SP7 If Not Cond1 and Not Cond2 and Not Cond3 Then SP8 Whe SP1 is a Sumproduct function that returns all rows SP2 returns all rows where M3 equals Column M on Details tab SP3 returns all rows where Column L equals F SP4 returns all rows where Column L equals F on Details tab And where M3 equals Column M on Details tab SP5 returns all rows where B3 equals Column B on Details tab SP6 returns all rows where B3 equals Column B on Details tab And then return all rows where M3 equals Column M on Details tab SP7 returns all rows where B3 equals Column B on Details tab And where Column L equals F SP8 returns all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab *** Required from You: 1. To obtain the above results, please confirm the arguments that repeat for each of SP1 to SP8. I assume these are still what I refered to as (AAA) to (DDD) in my previous post. 2. List for each (SP1 to SP8) the condition-specific arguments that (when multiplied by the repeating arguments) will return the desired results. *** My Assignment: Devise the formula. In other words, devise an IF structure that returns the correct condition-specific argument from the different options and multiply it together with the repeating arguments inside the Sum function. Greg |
Formula Question
Sorry if my last post was overly complicated. I was trying to clarify the
whole picture and probably achieved the opposite. I was hoping not to have to take the time to figure out what set of arguments are required to return each of the specified results listed in the "Where" section of my last post. For instance, what set of arguments would be required in a Sumproduct function to achieve this: Return all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab I was thinking something like this. These are just examples of arguments from your original post that I copied and pasted: SP1: --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F")... SP2: ...... etc. You already know this. It will make it easier for me if you sort this out. Greg |
Formula Question
I understand what you mean now. Here are all the conditions with the
arguments below them: SP1 is a Sumproduct function that returns all rows --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$3=All,$B$2:$B$49706<All) If($I$3=W/ Blanket Orders,$L$2:$L$49706)¦.will not equal anything since I want all rows If($M$3=All,$M$2:$M$49706<All) SP2 returns all rows where M3 equals Column M on Details tab --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$3=All,$B$2:$B$49706<All) If($I$3=W/ Blanket Orders,$L$2:$L$49706)¦.will not equal anything since I want all rows If($M$2:$M$49706=LeadTimes!$M$3) SP3 returns all rows where Column L equals F --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$3=All,$B$2:$B$49706<All) If($I$3=No Blanket Orders,$L$2:$L$49706=F) If($M$3=All,$M$2:$M$49706<All) SP4 returns all rows where Column L equals F on Details tab And where M3 equals Column M on Details tab --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$3=All,$B$2:$B$49706<All) If($I$3=No Blanket Orders,$L$2:$L$49706=F) If($M$2:$M$49706=LeadTimes!$M$3) SP5 returns all rows where B3 equals Column B on Details tab --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$2:$B$49706=LeadTimes!$B$3) If($I$3=W/ Blanket Orders,$L$2:$L$49706)¦.will not equal anything since I want all rows If($M$3=All,$M$2:$M$49706<All) SP6 returns all rows where B3 equals Column B on Details tab And then return all rows where M3 equals Column M on Details tab --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$2:$B$49706=LeadTimes!$B$3) If($I$3=W/ Blanket Orders,$L$2:$L$49706)¦.will not equal anything since I want all rows If($M$2:$M$49706=LeadTimes!$M$3) SP7 returns all rows where B3 equals Column B on Details tab And where Column L equals F --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$2:$B$49706=LeadTimes!$B$3) If($I$3=No Blanket Orders,$L$2:$L$49706=F) If($M$3=All,$M$2:$M$49706<All) SP8 returns all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0) If($B$2:$B$49706=LeadTimes!$B$3) If($I$3=No Blanket Orders,$L$2:$L$49706=F) If($M$2:$M$49706=LeadTimes!$M$3) The first part will be the same for each condition since I'll always be looking at the month and year and the next two arguments. I have another column on my worksheet that uses different criterias for the last two arguments on the top section. I can just change those once the formula is created for this current column we are working on. I don't want to confuse you more so I will leave it at that. Let me know if what I wrote about for the conditions makes sense to you. "Greg Wilson" wrote: Sorry if my last post was overly complicated. I was trying to clarify the whole picture and probably achieved the opposite. I was hoping not to have to take the time to figure out what set of arguments are required to return each of the specified results listed in the "Where" section of my last post. For instance, what set of arguments would be required in a Sumproduct function to achieve this: Return all rows where B3 equals Column B on Details tab And where Column L equals F And where M3 equals Column M on Details tab I was thinking something like this. These are just examples of arguments from your original post that I copied and pasted: SP1: --(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$N$2:$N$49706=1),--(Detail!$Q$2:$Q$49706=0),--(Detail!$L$2:$L$49706="F")... SP2: ...... etc. You already know this. It will make it easier for me if you sort this out. Greg |
Formula Question
Theoretical Array Formula :
= SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H)) Conditions List: Cond1 : $B$3 = "All" Cond2 : $I$3 = "W/ BLANKET ORDERS" Cond3 : $M$3 = "All" Arguments List: A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15)) B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15)) C : (Detail!$N$2:$N$49706=1) D : (Detail!$Q$2:$Q$49706=0) E : (Detail!$B$2:$B$49706<All) F : (Detail!$L$2:$L$49706) G : (Detail!$M$2:$M$49706<All) H : (Detail!$M$2:$M$49706=LeadTimes!$M$3) I : (Detail!$L$2:$L$49706=F) J : (Detail!$B$2:$B$49706=LeadTimes!$B$3) Putting it Together: = SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3 = "All", (Detail!$B$2:$B$49706<All), (Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS", (Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=F)) * IF($M$3 = "All", (Detail!$M$2:$M$49706<All), (Detail!$M$2:$M$49706=LeadTimes!$M$3))) Notes: 1. Commit the formula with Ctrl + Shift + Enter 2. I took the liberty to add "Detail!" to some of the ranges you gave me. I might have it wrong. Check the Arguments list. 3. As mentioned earlier, I expect the performance to be a bit disapointing. 4. I'm working on a project at this time and can't give it my undivided attention. Have my own post: http://tinyurl.com/yuc362 Hope it works !!! Greg |
Formula Question
Hi Greg,
The code looks great but for some reason I'm getting the old #VALUE! error when I put it into my spreadsheet. I checked, rechecked, and checked it again for errors but can't seem to find any. Can you see anything that might be causing this error? I'm committing the formula with Ctrl + Shift + Enter. Here is what I put into my file: =SUM((MONTH(Detail!$J$2:$J$12000)=MONTH(B15))*(YEA R(Detail!$J$2:$J$12000)=YEAR(B15))*(Detail!$N$2:$N $12000=1)*(Detail!$Q$2:$Q$12000=0)*IF($B$3="All",( Detail!$B$2:$B$12000<"All"),(Detail!$B$2:$B$12000 =LeadTimes!$B$3))*IF($I$3="W/ BLANKET ORDERS",(Detail!$L$2:$L$12000),(Detail!$L$2:$L$120 00="F"))*IF($M$3="All",(Detail!$M$2:$M$12000<"All "),(Detail!$M$2:$M$12000=LeadTimes!$M$3))) "Greg Wilson" wrote: Theoretical Array Formula : = SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H)) Conditions List: Cond1 : $B$3 = "All" Cond2 : $I$3 = "W/ BLANKET ORDERS" Cond3 : $M$3 = "All" Arguments List: A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15)) B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15)) C : (Detail!$N$2:$N$49706=1) D : (Detail!$Q$2:$Q$49706=0) E : (Detail!$B$2:$B$49706<All) F : (Detail!$L$2:$L$49706) G : (Detail!$M$2:$M$49706<All) H : (Detail!$M$2:$M$49706=LeadTimes!$M$3) I : (Detail!$L$2:$L$49706=F) J : (Detail!$B$2:$B$49706=LeadTimes!$B$3) Putting it Together: = SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3 = "All", (Detail!$B$2:$B$49706<All), (Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS", (Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=F)) * IF($M$3 = "All", (Detail!$M$2:$M$49706<All), (Detail!$M$2:$M$49706=LeadTimes!$M$3))) Notes: 1. Commit the formula with Ctrl + Shift + Enter 2. I took the liberty to add "Detail!" to some of the ranges you gave me. I might have it wrong. Check the Arguments list. 3. As mentioned earlier, I expect the performance to be a bit disapointing. 4. I'm working on a project at this time and can't give it my undivided attention. Have my own post: http://tinyurl.com/yuc362 Hope it works !!! Greg |
Formula Question
Hi Greg,
I found the problem. In this part of the formula is where it was causing the error: IF($I$3 = "W/ BLANKET ORDERS", (Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=F)) Apparently the "true" condition needed more than just the cell range. I put in this to replace it and call out all records since it will never = All (Detail!$L$2:$L$49706<"All") I haven't finished testing it yet but early results show that it works perfectly. Once I test it a bit more I will give you a final result. Thanks Greg! "Greg Wilson" wrote: Theoretical Array Formula : = SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H)) Conditions List: Cond1 : $B$3 = "All" Cond2 : $I$3 = "W/ BLANKET ORDERS" Cond3 : $M$3 = "All" Arguments List: A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15)) B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15)) C : (Detail!$N$2:$N$49706=1) D : (Detail!$Q$2:$Q$49706=0) E : (Detail!$B$2:$B$49706<All) F : (Detail!$L$2:$L$49706) G : (Detail!$M$2:$M$49706<All) H : (Detail!$M$2:$M$49706=LeadTimes!$M$3) I : (Detail!$L$2:$L$49706=F) J : (Detail!$B$2:$B$49706=LeadTimes!$B$3) Putting it Together: = SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3 = "All", (Detail!$B$2:$B$49706<All), (Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS", (Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=F)) * IF($M$3 = "All", (Detail!$M$2:$M$49706<All), (Detail!$M$2:$M$49706=LeadTimes!$M$3))) Notes: 1. Commit the formula with Ctrl + Shift + Enter 2. I took the liberty to add "Detail!" to some of the ranges you gave me. I might have it wrong. Check the Arguments list. 3. As mentioned earlier, I expect the performance to be a bit disapointing. 4. I'm working on a project at this time and can't give it my undivided attention. Have my own post: http://tinyurl.com/yuc362 Hope it works !!! Greg |
Formula Question
Hi Greg,
Everything is working great now! I thank you very much for all your work you put into this and helping me solve my problem. I greatly appreciate it. I wish you luck with your current project. I'm sure I'll see you on the board again. Take Care Larry "Greg Wilson" wrote: Theoretical Array Formula : = SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H)) Conditions List: Cond1 : $B$3 = "All" Cond2 : $I$3 = "W/ BLANKET ORDERS" Cond3 : $M$3 = "All" Arguments List: A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15)) B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15)) C : (Detail!$N$2:$N$49706=1) D : (Detail!$Q$2:$Q$49706=0) E : (Detail!$B$2:$B$49706<All) F : (Detail!$L$2:$L$49706) G : (Detail!$M$2:$M$49706<All) H : (Detail!$M$2:$M$49706=LeadTimes!$M$3) I : (Detail!$L$2:$L$49706=F) J : (Detail!$B$2:$B$49706=LeadTimes!$B$3) Putting it Together: = SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3 = "All", (Detail!$B$2:$B$49706<All), (Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS", (Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=F)) * IF($M$3 = "All", (Detail!$M$2:$M$49706<All), (Detail!$M$2:$M$49706=LeadTimes!$M$3))) Notes: 1. Commit the formula with Ctrl + Shift + Enter 2. I took the liberty to add "Detail!" to some of the ranges you gave me. I might have it wrong. Check the Arguments list. 3. As mentioned earlier, I expect the performance to be a bit disapointing. 4. I'm working on a project at this time and can't give it my undivided attention. Have my own post: http://tinyurl.com/yuc362 Hope it works !!! Greg |
Formula Question
Thanks for the feedback. I didn't think it would work on first try. This sort
of thing is just too difficult for that. Glad you were able to figure it out. All the best. Greg "Secret Squirrel" wrote: Hi Greg, Everything is working great now! I thank you very much for all your work you put into this and helping me solve my problem. I greatly appreciate it. I wish you luck with your current project. I'm sure I'll see you on the board again. Take Care Larry "Greg Wilson" wrote: Theoretical Array Formula : = SUM(A*B*C*D*IF(Cond1, E, J) * IF(Cond2, F, I) * IF(Cond3, G, H)) Conditions List: Cond1 : $B$3 = "All" Cond2 : $I$3 = "W/ BLANKET ORDERS" Cond3 : $M$3 = "All" Arguments List: A : (MONTH(Detail!$J$2:$J$49706)=MONTH(B15)) B : (YEAR(Detail!$J$2:$J$49706)=YEAR(B15)) C : (Detail!$N$2:$N$49706=1) D : (Detail!$Q$2:$Q$49706=0) E : (Detail!$B$2:$B$49706<All) F : (Detail!$L$2:$L$49706) G : (Detail!$M$2:$M$49706<All) H : (Detail!$M$2:$M$49706=LeadTimes!$M$3) I : (Detail!$L$2:$L$49706=F) J : (Detail!$B$2:$B$49706=LeadTimes!$B$3) Putting it Together: = SUM((MONTH(Detail!$J$2:$J$49706)=MONTH(B15))*(YEAR (Detail!$J$2:$J$49706)=YEAR(B15))*(Detail!$N$2:$N$ 49706=1)*(Detail!$Q$2:$Q$49706=0)*IF(Detail!$B$3 = "All", (Detail!$B$2:$B$49706<All), (Detail!$B$2:$B$49706=LeadTimes!$B$3)) * IF($I$3 = "W/ BLANKET ORDERS", (Detail!$L$2:$L$49706), (Detail!$L$2:$L$49706=F)) * IF($M$3 = "All", (Detail!$M$2:$M$49706<All), (Detail!$M$2:$M$49706=LeadTimes!$M$3))) Notes: 1. Commit the formula with Ctrl + Shift + Enter 2. I took the liberty to add "Detail!" to some of the ranges you gave me. I might have it wrong. Check the Arguments list. 3. As mentioned earlier, I expect the performance to be a bit disapointing. 4. I'm working on a project at this time and can't give it my undivided attention. Have my own post: http://tinyurl.com/yuc362 Hope it works !!! Greg |
| All times are GMT +1. The time now is 05:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com