Forum Discussion
Solved
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...
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.