I have the following formula on one of my worksheets but when cell B3 is
"All" I return the correct value but when it's not "All" I get an error
#VALUE!. What would cause this error in my formula? Everything looks correct
to me.

=IF($B$3="All",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$J$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$I$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000=YTDSummary!$B$3)))

Maybe this s/b J2:J50000?
--(YEAR(Detail!$I$2:$J$50000)


"Secret Squirrel" wrote:

I have the following formula on one of my worksheets but when cell B3 is
"All" I return the correct value but when it's not "All" I get an error
#VALUE!. What would cause this error in my formula? Everything looks correct
to me.

=IF($B$3="All",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$J$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$I$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000=YTDSummary!$B$3)))

Sometimes the obvious gets overlooked! Thank you for pointing that out. I
guess after staring at the screen for a while things look the way they should

Thanks!

"JMB" wrote:

Maybe this s/b J2:J50000?
--(YEAR(Detail!$I$2:$J$50000)


"Secret Squirrel" wrote:

I have the following formula on one of my worksheets but when cell B3 is
"All" I return the correct value but when it's not "All" I get an error
#VALUE!. What would cause this error in my formula? Everything looks correct
to me.

=IF($B$3="All",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$J$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$I$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000=YTDSummary!$B$3)))

Glad that was the only problem - thanks for posting back.

"Secret Squirrel" wrote:

Sometimes the obvious gets overlooked! Thank you for pointing that out. I
guess after staring at the screen for a while things look the way they should

Thanks!

"JMB" wrote:

Maybe this s/b J2:J50000?
--(YEAR(Detail!$I$2:$J$50000)


"Secret Squirrel" wrote:

I have the following formula on one of my worksheets but when cell B3 is
"All" I return the correct value but when it's not "All" I get an error
#VALUE!. What would cause this error in my formula? Everything looks correct
to me.

=IF($B$3="All",SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$J$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(MONTH(Detail!$J$2:$J$50000)=MONTH($B15)),--(YEAR(Detail!$I$2:$J$50000)=YEAR($B15)),--(Detail!$K$2:$K$50000=1),--(Detail!$B$2:$B$50000=YTDSummary!$B$3)))