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

2. 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)

3. 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. =SUMPRODUCT((LEFT(Sheet1!F2:F4816,2)="31")*(RIGHT( Sheet1!F2:F4816,1)="X")*(LEN(Sheet1!F2:F4816)=5),S heet1!H2:H4816)

5. Or better:
=SUMIF(Sheet1!F2:F4816,"31??X",Sheet1!H2:H4816)

6. 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. Would that include say 5000X or do you still only want items that start with '31'?

8. 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. 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. 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)

11. Hi Rory

Thanks for the help, much appreciated

Regards

Howard

12. 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. I'd go back to SUMIF for that
=SUMIF(Sheet1!F2:F4816,">=3000",Sheet1!H2:H4816)-SUMIF(Sheet1!F2:F4816,">3015",Sheet1!H2:H4816)

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

15. That won't matter to a SUMIF formula.

Page 1 of 2 12 Last

#### Posting Permissions

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