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

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

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

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

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

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