Countif Problem

  • Thread starter Thread starter Hamilton R. Romano
  • Start date Start date
H

Hamilton R. Romano

Hi all,

For example, if B1 = "20201" and I have "020201" in Range A1:A10 then
Countif(A1:A10,B1) = 1, but "20201" <> "020201" and the result should
be 0.
Is there a bug with this function?

Thanks
Hamilton R. Romano
 
Interesting ... I'd never noticed that. It does seem to be a bug. I tested
it with various formats but still got the same results.
 
Hi
Excel probably interprets both values as a date (which is in this case
equal)
 
Nope ... same result with "39201" and "039201", neither of which should be
interpreted as a date.

Regards,

Vasant.
 
Hi
o.k. seems to be really a bug. COUNTIF seems to coerce the values to
numbers (and skips the leading zero) (Assumption: you have entered the
values as text, e.g. with a leading apostrophe)
Very, very strange. So back to SUMPRODUCT :-)
=SUMPRODUCT(--(A1:A100=B1))
would return the correct result (zero in this case)
 
Thanks Vasant and Frank, but what can we do to force Microsoft to correct this?

Regards
Hamilton R. Romano
 
I will report it to MS and it will hopefully get added to a list.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Need Countifs Formula Help 0
countif formula 1
VBA Countif across sheets? 5
Sum / countif data range 2
VBA Syntax for Countif condition where only 1st char in Range of each cell = "M" 5
range names 4
Defining a range as a subset of cells in another range 12
Help with VBA............Again. 7

Back
Top