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.