=countif() does text comparisons.

So '1 and 1 will be counted.

You could use =sumproduct()

=sumproduct(--($e$1:$E$99=e1))

to distinguish between text and numbers.

But if your values are really numbers just with different numberformats, you'll
have to do something different.

About the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

bshorey wrote:

using Excel 2003

I have a list of invoice numbers and need to find duplicates. I tried using
conditional formatting in column E as such:

condition 1
formula is =COUNTIF(E:E,E1)1
(cell shading to yellow)

The problem I'm running into is that, for instance, it sees invoice numbers
0000003730 and 03730 as duplicates. I only want it to shade if it's an exact
match, i.e. 03730 and 03730.

I tried using a helper column (F) and in F2 put the formula
=IF(E2=E1,"duplicate","")

But I want ALL cells containing the same number to say "duplicate", i.e. all
occurences of 03730 should state duplicate, not just the second (or third or
fourth!) time it appears.

--

Dave Peterson