Countif function giving incorrect answer

[Anthony]

I have a weird issue where the Countif function is incorrectly giving
a result of 1, when the expected result is zero.

Cells A1:A3 are formatted as Text, and they have the following entered
in them:
A1 149902
A2 186427
A3 1541477

Cells B1:B3 are also formatted as text, and the have the following
entered in them:
B1 6-6120
B2 6-2310
B3 6-2410

In cell C1, i entered the following formula and filled down to C3:
=COUNTIF($A$1:$A$3,B1)

For every result in C1:C3, it is returning 1, even though i know that
the numbers in column B dont exist in column A and it should return
zero for each answer!

If you look at just the formula in C1, it thinks that it has found B1
(6-6120) in the value of A3 (1541477)
If i change A3 up or down by 1 (i.e 1541476 or 1541478), then the C1
formula returns 0: change it back to the orginal value, and it retruns
1?. The same happens for the other numbers as well. What is the
relationship between the 2 numbers for Countif to return 1?

Is there any explanation why this is occurring?

 

[T. Valko]

Is there any explanation why this is occurring?

COUNTIF evaluates text numbers and numeric numbers as being equal.

To see why you're getting those results do this...

Make sure cells A5:A7 are formatted as General.
*Manually* enter the numbers from A1:A3 in the cells A5:A7. *Do not* just
copy/paste the cells because you'll also be copying the TEXT format.

Now, format the cells A5:A7 as Date.
Compare those dates to the entries in cells B1:B3.

COUNTIF is evaluating cells B1:B3 as dates, which in Excel are really just
numbers formatted to look like dates, and the values in cells A1:A3 just
happen to be the date serial numbers of the entries in B1:B3.

Solution:

Use SUMPRODUCT:

=SUMPRODUCT(--(A$1:A$3=B1))

 

[ozgrid.com]

You should always avoid Text format unless you really do have text. Format
as number and for your B1:B3 values custom format like: 0-0000

 

[Ron Rosenfeld]

I have a weird issue where the Countif function is incorrectly giving
a result of 1, when the expected result is zero.

Cells A1:A3 are formatted as Text, and they have the following entered
in them:
A1 149902
A2 186427
A3 1541477

Cells B1:B3 are also formatted as text, and the have the following
entered in them:
B1 6-6120
B2 6-2310
B3 6-2410

In cell C1, i entered the following formula and filled down to C3:
=COUNTIF($A$1:$A$3,B1)

For every result in C1:C3, it is returning 1, even though i know that
the numbers in column B dont exist in column A and it should return
zero for each answer!

If you look at just the formula in C1, it thinks that it has found B1
(6-6120) in the value of A3 (1541477)
If i change A3 up or down by 1 (i.e 1541476 or 1541478), then the C1
formula returns 0: change it back to the orginal value, and it retruns
1?. The same happens for the other numbers as well. What is the
relationship between the 2 numbers for Countif to return 1?

Is there any explanation why this is occurring?

I would classify this as a bug.

The values in column B, even though formatted as TEXT, (and ISTEXT(B1)-->TRUE),
are apparently being interpreted as DATES by the COUNTIF function. And Excel
stores June 1, 6120 as 1541477 (number of days since 1/1/1900).

The same is occurring with the other entries.

Note that if you change to the 1904 date system, the COUNTIF formula will
return zero's.

=SUMPRODUCT(--($A$1:$A$3=B1)) returns the correct answer, so there is clearly
and inconsistency.

--ron

 

[ozgrid.com]

Should have said enter 6-6120 as 66120 in a cell PRE custom formatted as:
0-0000

 

[Dave Peterson]

Just to add to Biff's excellent response...

If you switch to the 1904 date (tools|options|calculation tab in xl2003 menus),
then the formulas evaluate to 0's.