19 Digit Order Ref # Problems

350Z Pilot

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

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
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
57
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
35,520
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
57
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
57
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,446
Messages
5,528,802
Members
409,836
Latest member
karnasrinivas

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top