COUNTIF issue

FKoenig

Board Regular
Joined
Jul 27, 2011
Messages
85
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:


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:

 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top