Countif formula issue

Question

Hi,

One of our report, facing peculiar issue. In the report, numbers are showing text format hence converting those text into number.. all number are showing same number but in text format, it is showing different numbers.

for example, if we convert these "6102022000000031","6102022000000033","6102022000000037" into numbers, it is showing 6102022000000030 for all the 3 numbers.. hence countif formula showing duplicates but actually not.

Have attached screenshot here. Please help me to resolve this issue.

Answer 1

Hi @K Krishna Kumar

> all number are showing same number but in text format, it is showing different numbers

   It is an expected behavior, as Excel stores only 15 significant digits in a number in one cell, and changes digits after the fifteenth place to zeroes.  
   For more, please refer to '[Last digits are changed to zeros when you type long numbers in cells of Excel][1]'.  

   In your example, each cell has 16 digits, just the last digits are different between them, if show them in Number format, the last digits will show as "0".   
   Please note Countif function will convert texts to numeric values when comparing, then it shows duplicates as you see.  

   Please try this formula `=COUNTIF(A:A,A2&"*")`, by adding wildcards "*", let the Countif formula to compare information in Text format.  
   ![178265-capture37.jpg][2]  

   ********************************************  
   If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   
   Note: Please follow the steps in [our documentation][1] to enable e-mail notifications if you want to receive the related email notification for this thread.  

   *********************************************  

   [1]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html  

   [1]: https://learn.microsoft.com/en-us/office/troubleshoot/excel/last-digits-changed-to-zeros  

   [2]: /api/attachments/178265-capture37.jpg?platform=QnA