SUMIFS on criteria range with leading zero in text

[STP470]

        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).

[#2 · Accepted solution]

@STP470 

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")

[#3]

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...

[#4]

@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().

[#5]

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.

[#6]

@STP470 

For that exists SUMPRODUCT

=SUMPRODUCT(B1:B3*(A1:A3="06") )