CountIf() comparison only looks at first 15 characters

Posted on 2012-08-17
Last Modified: 2012-08-20

I am trying to use the countif() function to detect duplicate records in an Excel 2007 worksheet. The criteria I need the function to evaluate is a string of 23 characters (all numbers, no text).

The problem I am having is that the comparison used by countif() only looks at the first 15 characters in the criteria expression. For example the countif function considers the 2 strings 1234567890123451 and 1234567890123452
to be equal..

Is there some workaround for this? Or maybe a more robust function?
Question by:FernandoClark
    LVL 10

    Expert Comment

    I do not get that result.  Can you post a sanitized sample with the formula you're using?

    I used: =COUNTIF(A1:A2,"1234567890123451")
    LVL 8

    Expert Comment

    by:Elton Pascua
    Looks like it has to do with the precision limit of Excel in large numbers. Here are some workarounds, one of which is converting to text. Another would be to append a text value like a dash at the end of the number to make it text.

    Open in new window

    LVL 92

    Accepted Solution

    If the value you are matching against is a cell reference, then you need to do something like this:


    Author Closing Comment

    That seems to work, although the cell must be formatted as text. Thanks, this was very helpful.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now