"ShadoShryke" wrote...
....
Sheet 1 Sheet 2 Formula
A1 *12450903093004001 12450903093004001 =COUNTIF([Sheet 1]$A$1:$A8,A1)
A2 12450903093004001 12450903093004002 =COUNTIF([Sheet 1]$A$1:$A8,A2)
A3 12450903093004001
A4 *12450903093004001
A5 *12450903093004002
A6 *12450903093004002
A7 *12450903093004002
A8 *12450903093004002
....

Welcome to the wonderful world of COUNTIF's (and SUMIF's) criterion
2nd argument. While online Help doesn't say so explicitly, using
different criteria demonstrates empirically that COUNTIF's 2nd
arguments are ALWAYS treated as TEXT STRINGS during function
evaluation, BUT those text strings can be criteria matching numbers.
This is the underlying cause of your users' trouble with it.

The values in Sheet1 above are 17 decimal digits long. Excel can only
represent these as TEXT STRINGS since Excel can handle numbers with no
more than 15 decimal digits of precision. For example, had the Sheet1!
A5 value above been typed in rather than loaded from a CSV file, Excel
would have converted it to 12450903093004000.

So I'm going to take your data above at face value, meaning I'm
stipulating that your Sheet1 and Sheet2 data are actually text.

The problem with COUNTIF is that it treats the text criteria

12450903093004001

as the NUMERIC comparison criterion

=12450903093004000

That is, it converts the text value 12450903093004001 to a numeric
value, which automatically causes Excel to lose the final digit (by
which I mean it's changed to zero). This is specifically a problem
with your COUNTIF criteria.

The next problem with COUNTIF is that it then interprets such criteria
as matching BOTH number values AND text strings which could be
converted into number values AS NUMBERS. That is, it converts the
values in its 1st argument's range TO NUMBERS before doing the
comparison. That loses the rightmost 2 decimal digits in the Sheet1
values. What you wind up with is a count of all values in Sheet1
converted to numeric 12450903093004000 against the criterion also
converted to numeric 12450903093004000, and that's why both of your
original formulas return 8 rather than 4.

You could confirm COUNTIF's perverse functionality using the following
simpler test. Enter the following in cells in a blank worksheet.

A1: 0
A2: '0 [that's a single quote first, then zero]
A3: =0
A4: ="0"

The formulas =COUNTIF(A1:A4,0) and =COUNTIF(A1:A4,"0") BOTH return 4.

The only way to force COUNTIF to do TEXT comparisons only is to
include wildcard characters in the 2nd arguments. Given your sample
data above, the formulas

=COUNTIF([Sheet 1]$A$1:$A8,A1&"*")

=COUNTIF([Sheet 1]$A$1:$A8,A2&"*")

would both have returned 4. However, if the Sheet1 range had included
strings of 18 or more decimal numerals beginning with
12450903093004001 or 12450903093004002, these would be included in the
revised COUNTIF formulas' results.

If your users want EXACT equality counts and COULD HAVE text values of
varying lengths, then they CAN'T use COUNTIF because COUNTIF simply
isn't reliable when working with long strings which could be converted
into numbers.

One alternative that would work is

=SUMPRODUCT(--(Sheet1!$A$1:$A$8=A1))

which NEVER converts values between text and numeric and NEVER
considers text and numeric values to be equal. With your text data,
this SUMPRODUCT formula would perform ONLY text comparisons, so it
wouldn't suffer from numeric conversion with loss of precision.