# SUMPRODUCT ISSUE with FINAL VARIABLE

1. ## 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

2. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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

3. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

if i put those in a workbook with no data both resolve to 0
so it must be something in those last 2 thats wrong
try them individually
=SUMPRODUCT(--(Sheet1!\$AK\$2:\$AK\$15000>=1))
=SUMPRODUCT(--(Sheet1!\$AK\$2:\$AK\$15000<=7))
see if you get value in either

4. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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

5. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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.

6. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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.

7. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

You're welcome.

8. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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.

9. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

I'd stop using Datedif, and instead just use

=W2-B2

10. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

=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.

11. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

I've just checked =isnumber(U79) and it's FALSE. I'm guessing this is the reason, but don't know a workaround.

When I amend the formatting to General I get 1 to 13 as results 01/01/1900

12. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

=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))

13. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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.

14. ## Re: SUMPRODUCT ISSUE with FINAL VARIABLE

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)