Forum Discussion
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 |
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 Replies
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)
- there is an error in the countif function tutorial
there is a mismatch use of the < and > symbols reference digit 85
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/
