# 19 Digit Order Ref # Problems

#### 350Z Pilot

##### Board Regular
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>

### Excel Facts

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

#### RoryA

##### MrExcel MVP, Moderator
Use SUMPRODUCT instead:

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

#### 350Z Pilot

##### Board Regular
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>

b5=trim(A5)

#### RoryA

##### MrExcel MVP, Moderator

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

ADVERTISEMENT

And what about my solution in #4?

#### 350Z Pilot

##### Board Regular
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

##### Well-known Member
Thanks for the reply.

Replies
16
Views
915
Replies
3
Views
34
Replies
20
Views
955
Replies
0
Views
47
Replies
3
Views
84

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