countif not working properly... why?

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

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

Which adblocker are you using?

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
Top