SUMPRODUCT ISSUE with FINAL VARIABLE

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

Are there any #VALUE! Errors within SHEET1!$AK$2:$AK$15000 ?

Wow - what a quick response - couldn't see the wood for the trees

There was a #value! error in a single cell in the column......thanks Jonmo1 and martindwilson for pointing this out.

I don't know if it's protocol to ask a further related question, but..

Column AK is a workaround which is a sum between dates i.e. =DATEDIF(B2,W2,"d") as I can't write this into the formula, but if you have a suggestion of how I can do this - perfect.

Thanks

Glad to help

Why can't you put the Datedif function in sumproduct ?

Also, datedif doesn't realy seem necessary, you could change it to just W2-B2

So in the sumproduct you could use
--(SHEET1!$W$2:$W$15000-SHEET1!$B$2:$B$15000>=1),--(SHEET1!$W$2:$W$15000-SHEET1!$B$2:$B$15000<=7))

Though I'd be against this myself.
Seems like the difference between the 2 dates might be a number you want to keep seperate on the sheet.
So it can be used again by other functions.

Also, combining calculations into 1 formula is not always better/more efficient than seperating them out to helper columns
Helper columns are NOT a bad thing.

You are right - the difference between the dates is a number and this is used further to see how many orders are requested for despatch <24hrs / 1-2 days / 2-3 days / 3-7 days / 28 days.

As this is the case, I agree, I'll leave the helper column in.

Thanks Again.

You're welcome.

Out of curiousity, I have tried this and get a #VALUE! error as the date range in the formula on occasion before the sales date - when it's been entered retrospectively.

i.e. Sales Date 04/14/14 Despatch Date 04/12/14 - these rows cause a #num! error resulting in a #VALUE! error in the sumproduct formula.

If it's an easy fix, i'd like toresolve for peacec of mind - if not I'll leave as is as it works.

I'd stop using Datedif, and instead just use

=W2-B2

=SUMPRODUCT(--(ISNUMBER(SEARCH("*"&$U$62&"*",BOOKINGS!$AH$2:$AH$15000,1))),--(BOOKINGS!$B$2:$B$15000>=$U$3+TIME(0,0,1)),--(BOOKINGS!$B$2:$B$15000<=$V$3+TIME(23,59,59)),--(BOOKINGS!$Y$2:$Y$15000="y"),--(BOOKINGS!$D$2:$D$15000="cash"),--(BOOKINGS!$AJ$2:$AJ$15000>=V$63),--(BOOKINGS!$AJ$2:$AJ$15000<=V$64),--(BOOKINGS!$AK$2:$AK$15000>=$AH79),--(BOOKINGS!$AK$2:$AK$15000<=$AI79),--(ISNUMBER(SEARCH("*"&$U79&"*",BOOKINGS!$AL$2:$AL$15000,1))))

Apologies, I know I tagged this as solved - but a further question. The final part of the formula again uses a helper column 'AL' with the formula =day(A2) formatted as ddd i.e. day of the week. The U79 cells contains Mon U80 Tue U81 Wed etc. through to Sun. The value returned is always zero.

Help Appreciated.

=DAY(A2) returns only a day number from 1 to 31.
Formatting that cell as ddd only makes the cell APPEAR to have Sun Mon Tue etc...
But it still actually only contains the number.

Try
=TEXT(A2,"ddd")

And in the sumproduct
,--(BOOKINGS!$AL$2:$AL$15000=LEFT($U79,3))

I'd found the =text() formula and was just working through the potential when you posted.

Save me a lot of time and works perfect.

Much appreciated.

You're welcome.