Hope someone can see the wood for the trees, here.
As shown on the attached Sum of first digits.xlsx, Col A has three-digit product numbers.
Can't see why the following formula does not identify and total only the values in Col B where the number in Col A starts with the first two digits in A1 ("11") and ignores any starting "12" or "13":
"=SUMIF(A1:A5,LEFT(A1,2),B1:B5)"
Any pointers or solutions accepted gratefully, as ever.
Ochimus
Hi
Use
Formula:
Please Login or Register to view this content.
Regards
That will never work because you have told it the criteria needs to have only 2 characters, but your criteria range contain s 3
Try this instead...
=SUMPRODUCT(--(LEFT($A$1:$A$5,2)=LEFT(A1,2)),$B$1:$B$5)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks to both for prompt response.
Should have been obvious to me that the formula had to look at the same number of digits in Col A as the "control" in A1.
Ochimus
Happy to help, thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks