# Count If Function To Count Frequency Of Long Numbers

1. ## 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  Register To Reply

2. ## 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  Register To Reply

3. ## Re: Count If Function To Count Frequency Of Long Numbers

Excellent. Thanks.  Register To Reply