Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts

    SUMPRODUCT problem

    I have used the following sumproduct formula and cant uderstand why it returns #value!


    =SUMPRODUCT(--(LEFT(Sheet1!F2:F4816,2)="31")*(RIGHT(Sheet1!F2:F4 816,1)="X")*(Sheet1!H2:H4816))



    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    You have text in some column H cells within the range you reference. Use:
    =SUMPRODUCT((LEFT(Sheet1!F2:F4816,2)="31")*(RIGHT( Sheet1!F2:F4816,1)="X"),Sheet1!H2:H4816)
    instead
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Rory

    Thanks for the help. If I need to include all 4 character numbers ending in an X , how dow I amend the formula? For eg 3000X, 3001X.

    Only when testing the data I found some two and three character numbers ending in an X which I do not want to include in the formula for eg 30X, 301X etc

    Your assistance in this regard is most appreciated

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    =SUMPRODUCT((LEFT(Sheet1!F2:F4816,2)="31")*(RIGHT( Sheet1!F2:F4816,1)="X")*(LEN(Sheet1!F2:F4816)=5),S heet1!H2:H4816)
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    Or better:
    =SUMIF(Sheet1!F2:F4816,"31??X",Sheet1!H2:H4816)
    Last edited by rory; 2013-04-05 at 08:49.
    Regards,
    Rory
    Microsoft MVP - Excel.

  7. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Rory

    Thanks for the help. If i want to set the parameters from say 3100X to 31015X , how do I set this in the formula?

    Regards

    Howard

  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    Would that include say 5000X or do you still only want items that start with '31'?
    Regards,
    Rory
    Microsoft MVP - Excel.

  9. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Rory

    Sorry I made a typo . How do I amend the SUMPRODUCT formula so that I can set a range of numbers from say 3100X to 3115X , 3000X to 3017X etc

    Regards

    Howard

  10. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,089
    Thanks
    13
    Thanked 37 Times in 36 Posts
    SUMIF could be used twice to create an "or" condition.

    =SUMIF(Sheet1!$F$2:$F$4816,"31??X",Sheet1!$H$2:$H$ 4816)+SUMIF(Sheet1!$F$2:$F$4816,"32??X",Sheet1!$H$ 2:$H$4816)

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    For the sort of bands you want:

    =SUMPRODUCT((LEFT(Sheet1!F2:F4816,4)>="3100")*(LEF T(Sheet1!F2:F4816,4)<="3115")*(RIGHT( Sheet1!F2:F4816,1)="X"),Sheet1!H2:H4816)
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. #11
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Rory

    Thanks for the help, much appreciated

    Regards

    Howard

  13. #12
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Rory

    The formula you provided works perfectly for adding up the range 3000X to 3015X

    I tried amending your formula to add the range 3000 to 3015 without the X and it gives me an incorrect result

    =SUMPRODUCT((LEFT(Sheet1!F2:F4816,4)>="3000")*(LEF T(Sheet1!F2:F4816,4)<="3015"),(Sheet1!H2:H4816))

    Your assistance would be most appreciated

  14. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    I'd go back to SUMIF for that
    =SUMIF(Sheet1!F2:F4816,">=3000",Sheet1!H2:H4816)-SUMIF(Sheet1!F2:F4816,">3015",Sheet1!H2:H4816)
    Regards,
    Rory
    Microsoft MVP - Excel.

  15. #14
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Rory

    Thanks for the help. The formula returns zero as the numbers in Col F appear as text. kindly amend your formula to accomodate this

    Regards

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2013-04-06 at 06:22. Reason: Attaching sample file

  16. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts
    That won't matter to a SUMIF formula.
    Regards,
    Rory
    Microsoft MVP - Excel.

Page 1 of 2 12 LastLast

Posting Permissions

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