- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2007 August
- 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

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

- RE: Formula Question posted by Secret Squirrel on Sun, 19 Aug 2007

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