Forum Discussion
SUMIFS on criteria range with leading zero in text
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")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.
- 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().
- 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.
