Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: COUNTIF issue

  1. #1
    Board Regular
    Join Date
    Jul 2011
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIF issue

    So I have two lists (A and B) of concatenated customer #'s and material #'s (to create unique identifiers) copied/pasted to values and formatted as text.

    List A is on a tab called "CPI" in cells X2 through X42766
    List B is on another tab "New" in cells A2 through A202


    I want to make sure that all of the records on list B are accounted for on list A... so I thought a simple COUNTIF would work.

    =COUNTIF(CPI!$X$2:$X$42766,New!A2)

    ... but I'm getting some strange results. For instance, one of the cells is returning a "7", but when I use the excel "find all" function on column "x" of the CPI tab for the same value, excel only finds 1 result.

    can anybody tell me what I've done to confuse COUNTIF? Is it something screwy about storing #'s as text maybe?

  2. #2
    Board Regular
    Join Date
    Jul 2011
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    How about an example. below is a list of 37 of the items from List A. If I put them in a new workbook (again, formatted as text) in cells A2:A38.

    Then populate cell C2 (formatted as text) with "487308572173431883" and put the following formula in D2=COUNTIF($A$2:$A$38,C2), the formula returns a "7". However, highlighting column A and Ctrl-F "finding all" the same string of #'s yeilds 1 find (combo #29 in cell A30).

    487308572173430570
    487308572173430573
    487308494805430696
    487308572219430705
    487308572202431079
    487308572202431081
    487308572219431142
    487308572219431143
    487308572173431188
    487308572202431216
    487308572173431236
    487308494805431279
    487308572219431279
    487308572219431288
    487308572127431290
    487308572173431290
    487308572219431290
    487308572202431292
    487308572219431299
    487308572202431347
    487308572173431362
    487308572219431370
    487308572219431371
    487308572173431375
    487308572202431490
    487308572173431735
    487308572219431735
    487308572041431745
    487308572173431883
    487308572202431886
    487308572219431999
    487308494805432026
    487308572219432137
    487308572173432141
    487308572173432595
    487308572173432734
    487308572219692911

  3. #3
    Board Regular
    Join Date
    Jul 2011
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    More data... it seems the records setting off the "COUNTIF" ARE #'S 9,11,16,21,24,26,AND 29. What do all of these records have in common? The same 1st 15 characters.

    Does anybody know if COUNTIF limits the string to 15 characters?

  4. #4
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    Hi,

    Try instead in D2 something like:

    =SUMPRODUCT(--(A2:A38=C2))

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,108
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: COUNTIF issue

    When I try and copy that data list into Excel they get converted into numbers 4.87E+17
    If I then select A2 it has changed into 487308572173430000 and I've lost the last few digits.
    The COUNTIF does indeed produce 7.

    If I paste the list into a text editor and add ' at the beginning then copy and paste that into Excel
    it retains all the digits.
    The COUNTIF then produces 0.

    When you select cell A2 what is the number Excel is displaying between the ribbon and the top of columns marked A,B,C etc, ie the actual value of A2?

  6. #6
    Board Regular
    Join Date
    Jul 2011
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    Looks like we have a winner, thank you.

    Two questions:
    1) Why double negative "--"
    2) Do you have any idea why the COUNTIF wouldn't work and why this does?

  7. #7
    Board Regular
    Join Date
    Jul 2011
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    Quote Originally Posted by Special-K99 View Post
    When I try and copy that data list into Excel they get converted into numbers 4.87E+17
    If I then select A2 it has changed into 487308572173430000 and I've lost the last few digits.
    The COUNTIF does indeed produce 7.

    If I paste the list into a text editor and add ' at the beginning then copy and paste that into Excel
    it retains all the digits.
    The COUNTIF then produces 0.

    When you select cell A2 what is the number Excel is displaying between the ribbon and the top of columns marked A,B,C etc, ie the actual value of A2?
    I saw the same issue with converting to a number and then back again, so here's what I did:
    copy into a txt editor (notepad or the like)
    Format the cells in range A2:A38 to text
    Copy for the text editor, paste into cells (should retain all 'digits')
    Make sure cell D2 is also formatted as txt before pasting the value into that cell.

    The ribbion should still display the full string - 487308572173431883 when cell D2 is selected, but will have a "number stored as txt" error.

  8. #8
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    Quote Originally Posted by FKoenig View Post
    1) Why double negative "--"
    The double negative converts TRUE and FALSE into 1 and 0 which can then be summed. Here is a nice explanation:


    The SUMPRODUCT function is very versatile. To explore further see the following articles:



    Quote Originally Posted by FKoenig View Post
    2) Do you have any idea why the COUNTIF wouldn't work and why this does?
    Yes, it is the 15 digit limit you identified earlier. See for example:


  9. #9
    Board Regular
    Join Date
    Jul 2011
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    Thanks very much!

  10. #10
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF issue

    You're welcome!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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