19 Digit Order Ref # Problems

[350Z Pilot]

Recently one of my vendors switched to a 19 digit Order Ref# and it's driving me nuts. Below is a simplified example where I'm counting the number of times the Ref# in cell A14 appears in range A1:A14. My formula, =COUNTIF(A1:A14,"="&A14), counts "4 times" but the correct answer is "1 time". NOTE: The Ref #s are formatted as TXT. Are the Ref #s just to big for Excel 2010 to handle? Any help would be appreciated.

Thanks in advance

1	Order Ref No
2	1408149900002224029

<tbody>
[TD="width: 31, bgcolor: transparent"] 3 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409039900002387409 [/TD]

[TD="width: 31, bgcolor: transparent"] 4 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002488939 [/TD]

[TD="width: 31, bgcolor: transparent"] 5 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409059900002518343 [/TD]

[TD="width: 31, bgcolor: transparent"] 6 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002490397 [/TD]

[TD="width: 31, bgcolor: transparent"] 7 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409039900002387599 [/TD]

[TD="width: 31, bgcolor: transparent"] 8 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409049900002533179 [/TD]

[TD="width: 31, bgcolor: transparent"] 9 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409059900002521827 [/TD]

[TD="width: 31, bgcolor: transparent"] 10 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002489247 [/TD]

[TD="width: 31, bgcolor: transparent"] 11 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002489554 [/TD]

[TD="width: 31, bgcolor: transparent"] 12 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002491259 [/TD]

[TD="width: 31, bgcolor: transparent"] 13 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002491446 [/TD]

[TD="width: 31, bgcolor: transparent"] 14 [/TD]
[TD="width: 198, bgcolor: transparent"] 1409089900002490802 [/TD]

[TD="width: 31, bgcolor: transparent"] 15 [/TD]
[TD="width: 198, bgcolor: transparent"][/TD]

[TD="width: 31, bgcolor: transparent"] 16 [/TD]
[TD="width: 198, bgcolor: transparent"] 4 [/TD]

[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 198, bgcolor: transparent"][/TD]

</tbody>

 

[RoryA]

Use SUMPRODUCT instead:

=SUMPRODUCT(--(A1:A14=A14))

 

[350Z Pilot]

I appreciate the suggestion but I may have over-simplified my problem. I use several different types of calculations (not just counting) that produce unreliable results. For example some formulas use SUMIFS (to get the Order Ref #) but for some reason the VALUE of my 19 digit Ref# does not translate into a number correctly. The example below shows the results I'm getting. Column A contains the Ref# while column be contains the =VALUE formulas. As you can see, I'm getting the wrong results.

[TABLE="width: 254"]
<colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="196" style="width: 147pt; mso-width-source: userset; mso-width-alt: 7168;"> <tbody>[TR]
[TD="width: 142, bgcolor: transparent"]1409089900005555550
[/TD]
[TD="width: 196, bgcolor: transparent"]=VALUE(A1)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1409089900005555551[/TD]
[TD="bgcolor: transparent"]1409089900005550000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1409089900005555552[/TD]
[TD="bgcolor: transparent"]1409089900005550000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1409089900005555553[/TD]
[TD="bgcolor: transparent"]1409089900005550000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1409089900005555554[/TD]
[TD="bgcolor: transparent"]1409089900005550000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Oeldere]

b5=trim(A5)

 

[RoryA]

You'll need to use SUMPRODUCT there too, for the same reason. SUMIF/COUNTIF will coerce the arguments to numbers in the equality test if they can, and since Excel only works with 15 digits of precision, all the numbers you just posted are the same as far as SUMIF/COUNTIF is concerned.

 

[350Z Pilot]

You'll need to use SUMPRODUCT there too, for the same reason. SUMIF/COUNTIF will coerce the arguments to numbers in the equality test if they can, and since Excel only works with 15 digits of precision, all the numbers you just posted are the same as far as SUMIF/COUNTIF is concerned.

Thanks Rory, I appreciate your time.

 

[Oeldere]

And what about my solution in #4?

 

[350Z Pilot]

Your answer only gives me a clean Ref#. As soon as I attempt to use the "clean Ref" in a VLOOKUP, COUNTIF, or SUMIF formula Excel rounds the last four digits to 0000. Which in-turn makes the whole Ref# useless to me.

But thanks

 

[Oeldere]

Thanks for the reply.