RE: Formula Question

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

Replies

In response to

Formula Question posted by Secret Squirrel on Sat, 18 Aug 2007