Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 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.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 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.

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

  6. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

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

  8. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 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)

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 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.

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

    Thanks for the help, much appreciated

    Regards

    Howard

  12. #12
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 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.

  14. #14
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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 07:22. Reason: Attaching sample file

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 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
  •