COUNTIF formula problem

[EOAEvan]

I am using a simple countif formula that is finding a match that is NOT there. I have 2 accounts #'s 71807150014327091095 & 71270543013027091003. The list it is looking in is a few hundred lines long. I have confirmed that the accounts are not in the list however the countif if is a returning 1 for the first and 3 for the second!?

I have used ctrl + f (find) as well as the match formula to confirm neither of the accounts are there. Both the lists of accounts as well as these 2 are formatted as text... Does countif have a character limit or some other factor that would be causing it to return false info? The reason I ask of the character limit is there are 3 accounts that are similar to the second one above. 71270543013027091001, 71270543013027091002 & 71270543013027091095

 

[Ron Coderre]

If you post your COUNTIF formula, we'll see if we can spot the issue.

 

[EOAEvan]

List of accounts are in column A.. those 2 accounts in column B. Formula in C... =COUNTIF(A5:A500,B1)

 

[EOAEvan]

It does seem to be a character limit problem.... I manually changed the digits starting from the right to a digit not used. The countif result kept reading 3 until I changed the 15th digit (left to right) then it changed to 0. I was not aware of any such limit??

 

[barry houdini]

Yes it's an oddity of COUNTIF. Excel only handles up to 15 significant figures for numbers....but it effectively applies that in COUNTIF too, only looking at the first 15 digits in your data......even if the data is formatted as text. Try using SUMPRODUCT instead

=SUMPRODUCT((A5:A500=B1)+0)

 

[EOAEvan]

ok, I will have to take this one step further to alter my code... The existing list is in column A, the new list is in Column V. Please let me know if you need an explaination as to what its doing.. How do I alter this to still work with the longer string of numbers? Here is what I am using currently.

Sub CompareColumnA2V()
Application.Calculation = xlCalculationManual
Dim LstRwA&, LstRwV&, bVal As Range
LstRwA = Cells(Rows.Count, "A").End(xlUp).Row
LstRwV = Cells(Rows.Count, "V").End(xlUp).Row
For Each bVal In Range("V5:V" & LstRwV)
  If Application.WorksheetFunction.CountIf(Range("A5:A" & LstRwA), bVal) = 0 Then _
    Cells(Rows.Count, "A").End(xlUp)(2).Value = bVal
Next
Range("A5:U" & Cells(Rows.Count, "A").End(xlUp).Row).Sort Key1:=Range("A5"), Order1:=xlAscending
Application.Calculation = xlCalculationAutomatic
End Sub