G
Guest
I need some help figuring out my formula problem. This formula is rather
large so maybe I'm missing something.
=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!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))
I'm getting the #VALUE problem when I use this formula. If I take out the
3rd statement and everything after it the first part of the formula works
fine. When I add this back into the formula I get the #VALUE error. Can
anyone see what the problem might be?
This is the part that I take out and it works fine.
IF($I$3="W/ BLANKET
ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))
large so maybe I'm missing something.
=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!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))
I'm getting the #VALUE problem when I use this formula. If I take out the
3rd statement and everything after it the first part of the formula works
fine. When I add this back into the formula I get the #VALUE error. Can
anyone see what the problem might be?
This is the part that I take out and it works fine.
IF($I$3="W/ BLANKET
ORDERS",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$49706)=MONTH(B15)),--(YEAR(Detail!$J$2:$J$49706)=YEAR(B15)),--(Detail!$M$2:$M$49706=1),--(Detail!$P$2:$P$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!$M$2:$M$49706=1),--(Detail!$P$2:$P$49706=0),--(Detail!$L$2:$L$49706="F"),--(Detail!$B$2:$B$49706=LeadTimes!$B$3)))