19 Digit Order Ref # Problems

350Z Pilot

Board Regular
Joined
Nov 18, 2008
Messages
64
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


1Order Ref No
21408149900002224029
31409039900002387409
41409089900002488939
51409059900002518343
61409089900002490397
71409039900002387599
81409049900002533179
91409059900002521827
101409089900002489247
111409089900002489554
121409089900002491259
131409089900002491446
141409089900002490802
15
164

<tbody>
</tbody>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use SUMPRODUCT instead:

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

350Z Pilot

Board Regular
Joined
Nov 18, 2008
Messages
64
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)
14090899000055555511409089900005550000
14090899000055555521409089900005550000
14090899000055555531409089900005550000
14090899000055555541409089900005550000
<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>
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Board Regular
Joined
Nov 18, 2008
Messages
64
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

Well-known Member
Joined
Dec 29, 2011
Messages
2,213

ADVERTISEMENT

And what about my solution in #4?
 

350Z Pilot

Board Regular
Joined
Nov 18, 2008
Messages
64
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,584
Members
430,557
Latest member
MK15

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?

Disable AdBlock

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
Top