The formula result in cell B7 of?*=SUMIFS(B1:B3, A1:A3, "06") is 7, while it should be 2...
The same problem with COUNTSIFS.
A possible tweak to the wrong result, is to add a letter in front of the leading zero texts (eg. "V006", "V06" and "V6" in the range A1:A3).
Yes, those functions will coerce the values to numbers, so 006, 06 and 6 will all be treated the same. You can use SUMPRODUCT instead:
=SUMPRODUCT(B3:B5*(A3:A5="06"))
or, since you have 365:
=SUM(FILTER(B3:B5,A3:A5="06"))
Everyone who confuses correlation and causation ends up dead.
Hi romperstomper.
Thank for the quick reply, and the nice suggestion to use SUM and FILTER instead. Works fine. :-)
Just say that the SUMIFS is not corrected by MS...
/Søren
I doubt that will be changed as that is the way it has always worked, and there are bound to be lots of people using it on the assumption it will continue to do just that.
Also try this
Formula:
Please Login or Register to view this content.
"A9" is criteria as 06
Last edited by avk; 10-01-2024 at 07:04 AM. Reason: add
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".
Cell B7 formula
Formula:Please Login or Register to view this content.
Hi wk9128. I guess this approach is not generally useful - at least I have not been able to tweak this to also give me the SUM of the criteria is just the '6 (resulting in the SUM being 4). But thanks anyway.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi and thanks for the reply. It works, but only if there is only one occurrence of '06 in the range...Originally Posted by avk
You have solutions in post #2. Please see post #8.
If MS do not want to change this for compatibility reasons, then MS could add an optional parameter with something like "Exact text comparison" to make the SUMIFS and COUNTIFS more "Exact"... (the same way as the VLOOKUP needs a 4th argument FALSE to have an exact match). Thanks again.
Thanks. I will do that later, just want to see if there are other good reply's.
If you are waiting for a SUMIF(S) version, then you will be waiting for a long time.
Please let us know what it is about the two succinct and effective solutions you have already that you feel may not be quite good enough so that we know what you are hoping to see.
They could, but you'd need to lobby them. I presume you realise that we aren't affiliated with MS in any way?If MS do not want to change this for compatibility reasons, then MS could add an optional parameter with something like "Exact text comparison" to make the SUMIFS and COUNTIFS more "Exact"... (the same way as the VLOOKUP needs a 4th argument FALSE to have an exact match).
I don't know why, but the result here is correct
If not, you can use this =SUMPRODUCT(B3:B5*(A3:A5="06"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks