Countif not working

[c mateland]

Excel 2003

I'm trying to do a simple countif. In column A, I have...

A
010020
010020
010020
010020
10020
10020

In another cell, I have =COUNTIF(A:A,"010020"), which results in 6,
when I expect 4. It's counting the 10020 entries as well. I want an
exact match to the literal criteria I enter.

How do I do that?

Thanks,
Chuck

 

[Ron Coderre]

Just use a wildcard to make Excel treat the criteria as text...

Try this
=COUNTIF(A1:A10,"010020*")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

 

[Ron Coderre]

My previously posted formula would count 010020a as a matched item.

A less risky alternative:
=SUMPRODUCT(--(A1:A10="010020"))

Note: =SUMPRODUCT(--(A:A="010020")) won't work.
You need to use a range that doesn't resolve to an entire column.

Regards,

Ron
Microsoft MVP (Excel)

 

[Ron Rosenfeld]

Just use a wildcard to make Excel treat the criteria as text...

Try this
=COUNTIF(A1:A10,"010020*")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

Learn something new every day here.
--ron