COUNTIF and leading zeros?
 

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

COUNTIF and leading zeros?
 

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.

--
Biff
Microsoft Excel MVP


"c mateland" wrote in message
oups.com...
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

COUNTIF and leading zeros?
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"c mateland" wrote in message
oups.com...
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

COUNTIF and leading zeros?
 

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.

--
Biff
Microsoft Excel MVP


"Lori" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"c mateland" wrote in message
oups.com...
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