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(D11:D33,"=?")

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

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

=COUNTIF(D11:D33,"~?")

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

On Jan 21, 7:49*pm, "Thomas M." wrote:
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(D11:D33,"=?")

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

I am curious as to whether or not the "?"
can be used with COUNTIF.

Yes it can.

Try it like this:

=COUNTIF(D11:D33,"~?")

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

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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(D11:D33,"=?")

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

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

"Pete_UK" wrote in message
...
The ? character is used as a wildcard to match any single character.
You can use this syntax:

=COUNTIF(D11:D33,"~?")

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

On Jan 21, 7:49 pm, "Thomas M." wrote:
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(D11:D33,"=?")

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

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" wrote in message
...
I am curious as to whether or not the "?"
can be used with COUNTIF.

Yes it can.

Try it like this:

=COUNTIF(D11:D33,"~?")

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

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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(D11:D33,"=?")

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

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.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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" wrote in message
...
I am curious as to whether or not the "?"
can be used with COUNTIF.

Yes it can.

Try it like this:

=COUNTIF(D11:D33,"~?")

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

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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(D11:D33,"=?")

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

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

Pete

On Jan 21, 11:13*pm, "Thomas M." wrote:
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

"Pete_UK" wrote in message

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

=COUNTIF(D11:D33,"~?")

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

On Jan 21, 7:49 pm, "Thomas M." wrote:



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(D11:D33,"=?")

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- Hide quoted text -

- Show quoted text -

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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" wrote in message
...
I am curious as to whether or not the "?"
can be used with COUNTIF.

Yes it can.

Try it like this:

=COUNTIF(D11:D33,"~?")

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

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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(D11:D33,"=?")

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







.

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.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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" wrote in message
...
I am curious as to whether or not the "?"
can be used with COUNTIF.

Yes it can.

Try it like this:

=COUNTIF(D11:D33,"~?")

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

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
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(D11:D33,"=?")

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







.