Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 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
    6,327
    Thanks
    3
    Thanked 216 Times in 199 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
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 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
    6,327
    Thanks
    3
    Thanked 216 Times in 199 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
    6,327
    Thanks
    3
    Thanked 216 Times in 199 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

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 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
    6,327
    Thanks
    3
    Thanked 216 Times in 199 Posts
    Would that include say 5000X or do you still only want items that start with '31'?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 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
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,570
    Thanks
    44
    Thanked 73 Times in 69 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
    6,327
    Thanks
    3
    Thanked 216 Times in 199 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
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 Posts
    Hi Rory

    Thanks for the help, much appreciated

    Regards

    Howard

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 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
    6,327
    Thanks
    3
    Thanked 216 Times in 199 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
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 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

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,327
    Thanks
    3
    Thanked 216 Times in 199 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
  •