COUNTIF ignores leading zero's

[MrFoxar]

I have two, technically unique text values, with leading zeros, countif is including them in the count for each.

i.e.

...
D17 = 01234
D18 = 1234

formula in column "C"
=IF(D17<>"",COUNTIF('Part Numbers'!B:B,D17),"")
=IF(D18<>"",COUNTIF('Part Numbers'!B:B,D18),"")

both countif's are resulting in 2, whereas they should be 1 since they are textually unique.

The cells themselves, on both the source and counting side are formatted as text.

[MrFoxar]

I continued to look up the problem on Google and discovered this article.

http://groups.google.com/group/micro...351d5605c1010c

Problem solved.

[shg]

That's very thoughtful of you to post back with your solution, thank you.

The article es excellent; I'm copying it here.

Harlan Grove

"ShadoShryke" <[email protected]> wrote...

...

> Sheet 1 Sheet 2 Formula
>A1 12450903093004001 12450903093004001 =COUNTIF([Sheet 1]$A$1:$A8,A1)
>A2 12450903093004001 12450903093004002 =COUNTIF([Sheet 1]$A$1:$A8,A2)
>A3 12450903093004001
>A4 12450903093004001
>A5 12450903093004002
>A6 12450903093004002
>A7 12450903093004002
>A8 12450903093004002

Welcome to the wonderful world of COUNTIF's (and SUMIF's) criterion 2nd argument. While online Help doesn't say so explicitly, using different criteria demonstrates empirically that COUNTIF's 2nd arguments are ALWAYS treated as TEXT STRINGS during function evaluation, BUT those text strings can be criteria matching numbers. This is the underlying cause of your users' trouble with it.

The values in Sheet1 above are 17 decimal digits long. Excel can only represent these as TEXT STRINGS since Excel can handle numbers with no more than 15 decimal digits of precision. For example, had the Sheet1!A5 value above been typed in rather than loaded from a CSV file, Excel would have converted it to 12450903093004000.

So I'm going to take your data above at face value, meaning I'm stipulating that your Sheet1 and Sheet2 data are actually text.

The problem with COUNTIF is that it treats the text criteria

12450903093004001

as the NUMERIC comparison criterion

=12450903093004000

That is, it converts the text value 12450903093004001 to a numeric value, which automatically causes Excel to lose the final digit (by which I mean it's changed to zero). This is specifically a problem with your COUNTIF criteria.

The next problem with COUNTIF is that it then interprets such criteria as matching BOTH number values AND text strings which could be converted into number values AS NUMBERS. That is, it converts the values in its 1st argument's range TO NUMBERS before doing the comparison. That loses the rightmost 2 decimal digits in the Sheet1 values. What you wind up with is a count of all values in Sheet1 converted to numeric 12450903093004000 against the criterion also converted to numeric 12450903093004000, and that's why both of your original formulas return 8 rather than 4.

You could confirm COUNTIF's perverse functionality using the following simpler test. Enter the following in cells in a blank worksheet.

A1: 0
A2: '0 [that's a single quote first, then zero]
A3: =0
A4: ="0"

The formulas =COUNTIF(A1:A4,0) and =COUNTIF(A1:A4,"0") BOTH return 4.

The only way to force COUNTIF to do TEXT comparisons only is to include wildcard characters in the 2nd arguments. Given your sample data above, the formulas

=COUNTIF([Sheet 1]$A$1:$A8,A1&"*")

=COUNTIF([Sheet 1]$A$1:$A8,A2&"*")

would both have returned 4. However, if the Sheet1 range had included strings of 18 or more decimal numerals beginning with 12450903093004001 or 12450903093004002, these would be included in the revised COUNTIF formulas' results.

If your users want EXACT equality counts and COULD HAVE text values of varying lengths, then they CAN'T use COUNTIF because COUNTIF simply isn't reliable when working with long strings which could be converted into numbers.

One alternative that would work is

=SUMPRODUCT(--(Sheet1!$A$1:$A$8=A1))

which NEVER converts values between text and numeric and NEVER considers text and numeric values to be equal. With your text data, this SUMPRODUCT formula would perform ONLY text comparisons, so it wouldn't suffer from numeric conversion with loss of precision.