COUNTIF counts wrong

[moe_von]

Hi, I found a problem using COUNTIF function, when trying count data as shown below:

Excel says that there is 7 matches. Looks like it cut last three digits for counting (this is only sample, but it's same on other values in sheet. ) Why? And how to fix this? This happens in Excel from Office 2010 and 2019 Pro, so this is not version relatable.

VALUE
04060330000802020
04060330000802022
04060330000802023
04060330000802024
04060330000802025
04060330000802028
04060330000802029

[#2 · Accepted solution]

@moe_von 

Excel uses only 15 digits. Any additional digit will be changed to 0.

You could format the values as text but unfortunately COUNTIF() does a type conversion (text to number).

For more information and a solution click here:

https://newtonexcelbach.com/2017/12/22/the-countif-bug-and-how-to-avoid-it/

[#3]

@moe_von 

Hello, I hope I can assist you with your question.

Change the formula for a more consistent one.

=SUMPRODUCT(N(VALUES=YOUR_CRITERIA))

Considering your values on column A and the number that you want to count on C1

=SUMPRODUCT(N(A1:A10=C1))

Or if you want to use COUNTIF

=COUNTIF(A1:A10;CHAR(173)&C1)

If the answer is what you are looking for, don't be shy on hit the like button.

Please don't forget to mark as Official/Best Answer to help the other members find it too.

[#4]

If the answer is what you are looking for, don't be shy on hit the like button.

Please don't forget to mark as Official/Best Answer to help the other members find it too.

[#5]

there is an error in the countif function tutorial
there is a mismatch use of the < and > symbols reference digit 85