+ Reply to Thread
Results 1 to 14 of 14

SUMPRODUCT ISSUE with FINAL VARIABLE

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT ISSUE with FINAL VARIABLE

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

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT ISSUE with FINAL VARIABLE

    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.

  6. #6
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT ISSUE with FINAL VARIABLE

    You're welcome.

  8. #8
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT ISSUE with FINAL VARIABLE

    I'd stop using Datedif, and instead just use

    =W2-B2

  10. #10
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #11
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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
    Last edited by Curtain; 04-15-2014 at 03:32 PM.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #13
    Registered User
    Join Date
    04-15-2014
    Location
    Southend,Kenya
    MS-Off Ver
    Excel 2003
    Posts
    7

    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. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT ISSUE with FINAL VARIABLE

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a Final Estimate Form and a Final Materials List based on Worksheet Results
    By Crunched For Time in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-20-2013, 09:58 PM
  2. Macro issue: Object variable or with block variable not set error
    By utgjmb1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-29-2013, 06:07 AM
  3. Replies: 1
    Last Post: 07-24-2013, 02:45 PM
  4. replacing lowest test grade with final if final is higher formula help
    By colbyclay in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2012, 02:48 AM
  5. Replies: 2
    Last Post: 08-12-2009, 07:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1