SOLVED

# COUNTIF counts wrong

Occasional Visitor

# COUNTIF counts wrong

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 Replies
Best Response confirmed by moe_von (Occasional Visitor)
Solution

# Re: COUNTIF counts wrong

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).

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

# Re: COUNTIF counts wrong

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.