But if we enter three text strings with 16 or more numerical characters, that are only different in the 16th or later characters, COUNTIF will say they are all the same:
This problem was reported by John Walkenbach at Daily Dose of Excel, back in 2006. The solution given then used the SUM function as an array function: =SUM((A1:A3=A1)*1). The alternative shown below gives the same results. Note that using SUM the function must be entered as an array, using Ctrl-Shift-Enter.
An alternative that does not require array function entry is to use the SUMPRODUCT function:
But after 11 years Lori Miller returned to Daily Dose of Excel with a way to get COUNTIF to work correctly. Precede the address of the criterion cell with CHAR(173)&:
CHAR(173) is a “soft hyphen” character, which will ensure that the contents of the data range are treated as text strings, rather than numbers, but is otherwise ignored. Now all numeric text strings of any length may be entered, and they will only be counted as being the same if they really are.