I have an issue with #VALUE error that I'm struggling to resolve. The Formula below works without issue and returns 2 - which is the correct value.
=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$U$62&"*",SHEET1!$AH$2:$AH$15000,1))),--(SHEET1!$B$2:$B$15000>=$U$3+TIME(0,0,1)),--(SHEET1!$B$2:$B$15000<=$V$3+TIME(23,59,59)),--(SHEET1!$Y$2:$Y$15000="y"),--(SHEET1!$D$2:$D$15000="cash"),--(SHEET1!$AJ$2:$AJ$15000>=V$63),--(SHEET1!$AJ$2:$AJ$15000<=V$64))
When I add in the final 2 variables, I get an #VALUE! error
=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$U$62&"*",SHEET1!$AH$2:$AH$15000,1))),--(SHEET1!$B$2:$B$15000>=$U$3+TIME(0,0,1)),--(SHEET1!$B$2:$B$15000<=$V$3+TIME(23,59,59)),--(SHEET1!$Y$2:$Y$15000="y"),--(SHEET1!$D$2:$D$15000="cash"),--(SHEET1!$AJ$2:$AJ$15000>=V$63),--(SHEET1!$AJ$2:$AJ$15000<=V$64),--(SHEET1!$AK$2:$AK$15000>=1),--(SHEET1!$AK$2:$AK$15000<=7))
I've checked the data in Column AK and replaced and blanks with '0' and ensured that the content does not contain text, but still #VALUE!
U3 and V3 is a From and To Date Range U62 is text
Help would be much appreciated.
Regards
Bookmarks