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
3	1409039900002387409
4	1409089900002488939
5	1409059900002518343
6	1409089900002490397
7	1409039900002387599
8	1409049900002533179
9	1409059900002521827
10	1409089900002489247
11	1409089900002489554
12	1409089900002491259
13	1409089900002491446
14	1409089900002490802
15
16	4

<tbody>
</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.

1409089900005555550	=VALUE(A1)
1409089900005555551	1409089900005550000
1409089900005555552	1409089900005550000
1409089900005555553	1409089900005550000
1409089900005555554	1409089900005550000

<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> </tbody>

 

[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.