+ Reply to Thread
Results 1 to 3 of 3

Count If Function To Count Frequency Of Long Numbers

  1. #1
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2013
    Posts
    85

    Count If Function To Count Frequency Of Long Numbers

    I am using the COUNTIF function in VBA to count the number of times numbers like 450032027446442502730 appear in a column. Unfortunately, COUNTIF seems to treat 450032027446442502730 exactly the same as a number that is the same apart from the last few digits. For instance WorksheetFunction.CountIf(ActiveSheet.Range("A1:A4"),"450032027446442502730") would return 4 rather than 3 where the values below populate cells A1 to A4.

    450032027446442502730
    450032027446442502730
    450032027446442502745 (different last 2 digits)
    450032027446442502730


    If think this is because it interprets these numbers like 450032027446440000000 or something, treating the last however many digits as zeroes.

    Hope this makes sense. Many thanks in advance.

    Rowan
    Last edited by RowanB; 08-21-2013 at 05:19 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,977

    Re: Count If Function To Count Frequency Of Long Numbers

    Yes, Excel has a precision of 15 digits for numbers, and those long strings are treated as numbers because they only contain digits. If you change your values to include a text character, like this:

    _450032027446442502730
    _450032027446442502730
    _450032027446442502745
    _450032027446442502730

    and you use this:

    WorksheetFunction.CountIf(ActiveSheet.Range("A1:A4"),"_450032027446442502730")

    then you might have a better result.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2013
    Posts
    85

    Re: Count If Function To Count Frequency Of Long Numbers

    Excellent. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 12-22-2012, 05:04 PM
  2. Need Help with Macro to count the frequency of numbers in multiple columns and rows
    By crissycr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2012, 02:11 AM
  3. [SOLVED] How do i count numbers and letters to find a total count of all
    By Linda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-10-2005, 12:55 PM
  4. [SOLVED] Count the frequency of all numbers in a column
    By horatio in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-16-2005, 10:06 PM
  5. Using FREQUENCY Function to Count
    By Sergio in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1