Max number of characters countif recognizes

[Troutwater]

Hi Group,

I've used countif quite a lot but this one has got me confused.

In a column C >17,000 records has a 16 digit number (also tried text) value.
I want to count the number of occurances of each 16 digit value through the 17,000 rows.
All three countif functions return 3.

[TABLE="width: 904"]
<TBODY>[TR]
[TD]=COUNTIF($C$2:$C$17000,C22)</SPAN>
[/TD]
[TD]1000842281181008</SPAN>
[/TD]
[/TR]
[TR]
[TD]=COUNTIF($C$2:$C$17000,C23)</SPAN>
[/TD]
[TD]1000842281181009</SPAN>
[/TD]
[/TR]
[TR]
[TD]=COUNTIF($C$2:$C$17000,C24)</SPAN>
[/TD]
[TD]1000842281181008</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

But as you can see the last digit is different in the middle row than the upper and lower rows.

I tried pulling this data out into a separate sheet but even with only three 16 digit values the countif returns 3.

I can change the last digit to any other digit of any of the three 16 digit values and it still returns 3. So when I remove the last digit and make it 15 digit values, the other countif functions rule it out and countif equals 2 and the countif of the one 15 digit value equals 1.

Has countif got a max number of characters it can compare?

What's the problem? What can I do?

troutwater

 

[circledchicken]

Hi,

Yes, you are correct - there is a 15 digit limit. Try a SUMPRODUCT alternative instead, something like:

=SUMPRODUCT(--(C$2:C$17000 = C22))

 

[Troutwater]

Nope that's not working or at least I haven't figured it out.

If the limit is fifteen, I can get around it by truncating off the leading character without affecting the final count.

A little messy but gotta forge ahead.

But that one eloquent solution is still welcome here.

 

[Scott Huish]

I'm not sure why the SUMPRODUCT suggestion didn't work for you:

Excel Workbook
	C	D	E	F
2	1000842281181008			2
3	1000842281181009			1
4	1000842281181008			2
Sheet1

 

[Rick Rothstein]

If your 16-digit numbers are in cells formatted as Text, you can get your COUNTIF function calls to work the way you want by forcing the second argument to be Text (thus stopping Excel from converting them to numerical values and, thus, losing that last digit). One way to do this is by concatenating a wildcard onto the cell value in the second argument (highlighted in red below). So, this formula in place of the first one you posted should work correctly for you...

=COUNTIF($C$2:$C$17000,"*"&C22)

 

[circledchicken]

SUMPRODUCT works for me too as per Scott's example.

Depending on exactly what you're trying to achieve, another alternative might be to use a PivotTable instead to get the counts. As your data set seems to be quite large, that would likely be much faster as well.

 

[Troutwater]

If your 16-digit numbers are in cells formatted as Text, you can get your COUNTIF function calls to work the way you want by forcing the second argument to be Text (thus stopping Excel from converting them to numerical values and, thus, losing that last digit). One way to do this is by concatenating a wildcard onto the cell value in the second argument (highlighted in red below). So, this formula in place of the first one you posted should work correctly for you...

=COUNTIF($C$2:$C$17000,"*"&C22)

Rick,
I liked your answer the best. As I did learn something about a formula I'v used for so long. It'll be easier later to recognize this solution and know what happened.

 

[Rick Rothstein]

Rick,
I liked your answer the best. As I did learn something about a formula I'v used for so long. It'll be easier later to recognize this solution and know what happened.

Just to point out... this works for you current setup because your code numbers are all the same length (number of digits)... if that were not the case, then the possibility of erroneous matches could take place... for example, looking for 12345 and accidentally matching 99912345 (the asterisk would match the 999 part).