ExcelBanter - Countif not working

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Countif not working (https://www.excelbanter.com/excel-worksheet-functions/156769-countif-not-working.html)

Countif not working

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

Countif not working

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)

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

Countif not working

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 Coderre" wrote in message
...
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)

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

Countif not working

On Sun, 2 Sep 2007 20:40:57 -0400, "Ron Coderre"
wrote:

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