# countif not working properly... why?

#### Sayre

##### Board Regular
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.

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCD
11088207510006201
2108820751000620.004
3
Sheet1

#### VoG

##### Legend

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,167,961
Messages
5,856,531
Members
431,819
Latest member
Tori Murphy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back