# RE: Formula Question

 Subject: RE: Formula Question Posted by: Greg Wilson Date: Sun, 19 Aug 2007

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))*(YEAR(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

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