COUNTIF and leading zeros?

[c mateland]

Excel 2003

I'm trying to run a very simple COUNTIF function, and it's not
working. I have a list in column A of many product numbers. They are
all set as text. Some have alpha characters others are pure numbers
with some having leading zeros and others do not. (i.e. T002390, 2390,
002390) The list includes both 2390 and 002390 but only one occurance
each.

With...
F2 = 2390
or
F2 = 002390

I create the function...
=COUNTIF(A:A,F2)

With either criteria, the result is 2 instead of the expected 1.

How can I get the COUNTIF to discern between 2390 and 002390? Is there
a better formula for this purpose?

Thanks,
Chuck

 

[T. Valko]

I'm assuming F2 is also formatted as TEXT.

Try this:

=COUNTIF(A:A,F2&"*")

COUNTIF evaluates text numbers and numeric numbers as being equal. Using the
wildcard forces COUNTIF to evaluate the criteria as text.

 

[Lori]

But product numbers beginning "2390..." such as "239012" shouldn't be
included. Maybe try one of:

=COUNTIF(A:A,F2&"*")-COUNTIF(A:A,F2&"?*")

=SUM(COUNTIF(A:A,F2&{"*","?*"})*{1,-1})

 

[T. Valko]

If that's a possibility then I'd use:

=SUMPRODUCT(--(A1:A100=F2))

Note that SUMPRODUCT won't work using entire columns as range references
unless you're using Excel 2007.