Using the Question Mark With COUNTIF

[Thomas M.]

Excel 2007

I have a small table that shows me the status of each item on the list. The
status can be "Y", "N", "NA", or "?". I am using the COUNTIF function to
tell me how many of each status is on the list. For items with a status of
"?", which denotes an unknown status, I simply take the total number of
items on the list and subtract the sum of the other status values to arrive
at a count of items with an unknown status. This works fine.

However, I am curious as to whether or not the "?" can be used with COUNTIF.
I've tried the following:

=COUNTIF(D1133,"=?")

Figuring that the question mark is a special character and that a special
syntax might be needed in order make Excel interpret it literally, I've
played around with different variations using single and double quotes, but
I've been unable to make the formula work.

Is it possible to use "?" with COUNTIF, and if so what is the correct
syntax?

Thanks for any help that you can offer.

--Tom

 

[Pete_UK]

The ? character is used as a wildcard to match any single character.
You can use this syntax:

=COUNTIF(D1133,"~?")

to count the number of ? directly. The tilde symbol, ~, tells Excel to
treat the character that follows as a specific character rather than a
wildcard.

Hope this helps.

Pete

 

[T. Valko]

I am curious as to whether or not the "?"

can be used with COUNTIF.

Yes it can.

Try it like this:

=COUNTIF(D1133,"~?")

The tilde is an "escape character". It tells Excel to look for the literal
character "?" and not to evaluate it as the wildcard character ?.

 

[Thomas M.]

Thanks. That's exactly what I was looking for. I knew from previous
experience what the general issue was, but just couldn't come up with the
syntax to solve the problem.

--Tom

The ? character is used as a wildcard to match any single character.
You can use this syntax:

=COUNTIF(D1133,"~?")

to count the number of ? directly. The tilde symbol, ~, tells Excel to
treat the character that follows as a specific character rather than a
wildcard.

Hope this helps.

Pete

 

[Thomas M.]

Thanks for the information. I knew it was something like that because way
back when I was basically an expert in early versions of Excel, I had dealt
with similar issues. So I knew generally what the issue was, but couldn't
find the right syntax. Thanks for the help.

--Tom

 

[T. Valko]

The same technique applies when wanting to count the * (asterisk) character.
The * is also a wildcard.

=COUNTIF(range,"~*")

It can get kind of confusing!

If you had some weird data entries like ~*, then:

=COUNTIF(range,"~~~*")

That's 3 tildes asterisk.

 

[Pete_UK]

You're welcome, Tom - thanks for feeding back.

Pete

 

[Teethless mama]

If you had some weird data entries like ~*, then:

=COUNTIF(range,"~~~*")

That's 3 tildes asterisk.

It will do with 2 tildes asterisk

 

[T. Valko]

If you had some weird data entries like ~*

It will do with 2 tildes asterisk

Try it on this data:

A1: ~
A2: ~*
A3: ~~*
A4: *

=COUNTIF(range,"~~*") = 3

The 1st tilde tells it to evaluate the 2nd tilde as the literal tilde
character. The asterisk is being evaluated as a wildcard. So, it means:
count cells that contain a tilde character followed by anything or nothing.

=COUNTIF(range,"~~~*") = 1 (counting only A2 as it should)

The 1st tilde tells it to evaluate the 2nd tilde as the literal tilde
character and not an "escape character". The 3rd tilde tells it to evaluate
the asterisk as the literal asterisk character and not a wildcard.