|
Formula Problem
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))) |
Formula Problem
"),--(Detail!$B$2:$B$49706<"All")))),IF($I$3="W/ BLANKET
remove bracket ... "),--(Detail!$B$2:$B$49706<"All"))),IF($I$3="W/ BLANKET HTH "Secret Squirrel" wrote: 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))) |
Formula Problem
Perfect! Thanks for your help. When a formula is that big it's sometimes hard
to see the little things. "Toppers" wrote: "),--(Detail!$B$2:$B$49706<"All")))),IF($I$3="W/ BLANKET remove bracket ... "),--(Detail!$B$2:$B$49706<"All"))),IF($I$3="W/ BLANKET HTH "Secret Squirrel" wrote: 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))) |
| All times are GMT +1. The time now is 08:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com