countif not working properly... why?

[Sayre]

In cell A1 I have the following value: 108820751000620
In cell A2 I have the following value: 108820751000620.004

They are in text format.

In cell B1 I have the following formula: "=COUNTIF(A:A,A1)"
In cell B2 I have the following formula: "=COUNTIF(A:A,A2)"

There is nothing else on the entire spreadsheet.

The result of both of these formulas is coming up as 2, so Excel must be regarding the two values in Col A as the same thing, but they are clearly not. What could possibly be causing this?

When I try to change them from text format to general format using the Text To Columns method, the ".004" portion of cell A2 disappears and then the two cells really do equal each other. This is baffling to me and I don't think Excel was behaving this way before. I had been fiddling with the "Precision As Displayed" and "maximum Change" commands from the Options menu earlier, but I have since turned off Precisions As Displayed and set the Maximum Change to 0. Perhaps that's what's causing this but I don't know what to do to fix. I just want the Countif formulas to work properly and recognize that the values in cells A1 and A2 are different/unique.

Any ideas? I am using Excel 2003.

 

[Peter_SSs]

Sayre

You will likely get a more definitive answer from somebody else in due course, but I can certainly replicate the issue.

I think the COUNTIF is trying to interpret the data as numbers, even though the cells are formatted as text. Since Excel 2003 has a limit of 15 digits for numbers (and this is the issue that is causing the behaviour mentioned in your final paragraph), I think it is chopping the second one off after 15 digits and therefore seing the two values as equal.

In the meantinme, this might help. Note that you cannot have whole column references in SUMPRODUCT

Excel Workbook
	A	B	C	D
1	108820751000620		1
2	108820751000620.004
3
Sheet1

 

[VoG]

Duplicate http://www.mrexcel.com/board2/viewtopic.php?t=289473