Forum Discussion
SUMIFS on criteria range with leading zero in text
A B
1 '006 1
2 '06 2
3 '6 4
4
5 Total =SUMIFS(B1:B3, A1:A3, "06")
The formula result 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).
SOMEIFS functions converts texts which looks like numbers into the number. Thus resulting criteria is number 6.
Trick is to add CHAR(173) or use SUMPRODUCT
=SUMIFS(B1:B3, A1:A3, CHAR(173) & "06")
SOMEIFS functions converts texts which looks like numbers into the number. Thus resulting criteria is number 6.
Trick is to add CHAR(173) or use SUMPRODUCT
=SUMIFS(B1:B3, A1:A3, CHAR(173) & "06")- Thanks SergeiBaklan. 🙂
Your solution works fine - adding the CHAR(173). A little strange that it works however. But IT WORKS!
Only a little sad that MS does not fix the SUMIFS formula...STP470 , you are welcome.
Texts to numbers was by design from very beginning, about 30 years ago. Microsoft keeps that for compatibility. Trick with CHAR(173) was found about 20 years ago. In general more than another 2000 characters work if we use UNICHAR().
Would it be possible to ask MS to update the documentation on the SUMIFS and COUNTIFS functions, to add an text describing that with text with leading zeros, the leading zeros is dropped by the functions, and also the 2 workarounds (adding the CHAR(173) or instead using SUMPRODUCT function)?
Yes, it's possible. You may add your suggestion on Excel Feedback portal Excel · Community (microsoft.com) Or vote for similar one if it exists on Feedback portal.
