# COUNTIF formula problem

#### EOAEvan

##### Active Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If you post your COUNTIF formula, we'll see if we can spot the issue.

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

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??

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)

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.

Code:
``````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``````

Replies
4
Views
75
Replies
7
Views
543
Replies
7
Views
163
Replies
1
Views
329
Replies
7
Views
167

1,196,042
Messages
6,013,049
Members
441,746
Latest member
ArtemisAlex

### 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?

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