LinkBack URL
About LinkBacks
hi all,
I am unable to get the exact result for sumifs as the formula seems like overlook the "0" in front of the serial number or "." behind the serial number.
Many thanks for your help.
sumifs formula.PNG
hi all,
I am unable to get the exact result for sumifs as the formula seems like overlook the "0" in front of the serial number or "." behind the serial number.
Many thanks for your help.
sumifs formula.PNG
Use SUMPRODUCT function:
=SUMPRODUCT((Raw!$C$2:$C$7=$A2)*(Raw!$D$2:$D$7=$B2)*(Raw!$A$2:$A$7=C$1),Raw!$E$2:$E$7)
look lkes SUMIF(S) treats 123. as 123.0 as 123; 0123 as 123
use SUMPRODUCT instead of SUMIFS as Phuocam's solution.
Quang PT
The reason the SUMIFS is not working as expected, is because the numbers you are trying to add, are actually text that looks like a number.
A quick look will show that the numbers are left-aligned, by default, real numbers are right-aligned and text is left aligned. Also, unless you use special formatting, no real number will display with a leading 0 (D7), nor with just showing a decimal point with nothing after it (D4)
Finally, use this as a quick test...
=ISNUMBER(D2)
copied down.
If it shows FALSE, that means the numbers are text and will need to be converted
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
Yes go with Phuocam solution.
But can you explain about "serial" column 854401.
Any specific reason for dot after value. If not any reason then why not "A" or "B"
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
avk, that is probably an import from another program/application
Dear all,
I have solved the problem with Phuocam's method.
thank you very much for all your help.
thanks Phuocam! it works
It's good to know how to fix this, but, I think, it is also important to know what causes it
Thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks