Problem with COUNTIF formula

[Marcol]

When trying to answer a thread I came across this situation when trying to use COUNTIF to reveal duplicates.

The OP has an 18 digit serial number stored as text, I thought

Please Login or Register  to view this content.

Dragged Down
would show duplicates if the returned value was greater than 0, the result was not at all what I was expecting. (See the attached workbook)

Step 2 I tried MATCH as a check and this does return what I was expecting.

Step 3 was to convert the text string to a number and run the COUNTIF again, still seems wrong

Step 4 concatenated the string with a trailing "A" and the COUNTIF now returns the expected result

Why does the original formula apparently not work?....


Slainte
Alistair

[Cutter]

Check your $ signs in your formula.

[Cutter]

Try this:

=COUNTIF($A$1:$A1,$A2)

[Cutter]

Actually I just looked at your sheet and saw that you have headers. So the first COUNTIF() should be:

=COUNTIF($A$1:$A2,$A2)

[DonkeyOte]

Why does the original formula apparently not work?

COUNTIF will treat the numeric string as a number and will work to 15 digit significance - hence your results in C.

To see this "in action":

Please Login or Register  to view this content.

M2 onwards will ape C2 onwards

[Cutter]

And

would show duplicates if the returned value was greater than 0

it shows dupes if returned value greater than 1 - not 0

[Marcol]

Hi Cutter,
That's not the dilema, the same formula works fine with the concatenated string. Column "J"

The problem seems to be with long integers (overflow? or truncation?), but why does it not work with the original numeric string formatted as text?

[Marcol]

I was coming round to that thinking Don (see last post), but why should the MATCH formula read the string as a string of digits and COUNTIF treat the string as a number despite the formatting?

[DonkeyOte]

That's just how COUNTIF works - you can't count numeric strings as strings.

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

will both return the same result - 3

COUNTIF will treat things that look like numbers as numbers and will operate to 15 digit significance as you'd expect.

[Cutter]

Does sorting work the same way?

I tried a sort on the serial 'numbers' and they don't sort properly.

[Marcol]

I'm behind the curve here, slow broadband connection yet again.

Cheers Don that indeed explains the apparent discrepancy.

One final question
Why 15 characters?
It doesn't seem to tally with

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647

[DonkeyOte]

Does sorting work the same way?

I don't really understand the question - the Serials though strings will sort in numerical order given they are of a consistent length.

The behaviour outlined by Marcol is very much an idiosyncrasy of COUNTIF/SUMIF - at least it is to my knowledge.

Why 15 characters

Excel works to 15 significant digits (thereafter converted to 0) - see: http://support.microsoft.com/kb/65903

[Marcol]

Good point Cutter SORT seems to have the same problem with this type of data.

That I would never have expected!

[Cutter]

Sorry for jumping in on another OP's thread but I feel it's related.

I preformatted a range of cells as text and then took Marcol's list of serial numbers from col A and did Paste Special - Values into that text formatted range.
I then did a sort ascend on that range.
Just a quick look at it showed the first 3 not sorted to last characters.
Result is:

899720203566848507
899720203566848945
899720203566848499

The first 15 digits are the same for all 3 but the last 3 vary and are not sorted properly.

[DonkeyOte]

Did you choose to sort the numbers as text or number ?

If you opt to sort treating things "that look like a number as a number" then Excel will work to 15 digits - hence the last 3 digits are ignored.
Conversely should you sort as Text it should sort in "numerical" order given consistent string length.

[Cutter]

I wasn't even aware of that distinction. I just hit the Sort Ascend button in the QAT.

So I guess, by default it sorts by "looks like a number, sort by a number"

That explains why, by adding an "A" by formula to the serials, then copy paste values to get rid of the formulas, it does sort properly.

Another thing to keep in mind for long numbers (or their clones). I keep forgetting about that 15 digit limitation.

Thanks Don and Marcol.

[Marcol]

Again behind the cuve.

This amended workbook illustrates Dons' last point

Cheers guys.
Two problems solved for the price of one!
.