COUNTIF with wildcards
 

This question is bugging me to no end, primarily because the answer is easily
available - every resource I've found online tells me that countif works with
wildcards, but for the life of me I can't get it to work. I must be doing
something wrong with the syntax, but I have no idea what. Basically I want to
find out how many cells in a range contain values between 1000 and 1999. I
can solve it with a "greater than, smaller than" combination, but it seems
somewhat more elegant to use a wildcard... only it doesn't work. This is what
I have:

In column A, I have the following values:

A1 1000
A2 1200
A3 1100
A4 1000
A5 1000
A6 1000
A7 1200

And in B1 I have the formula "=COUNTIF(A1:A7;"1*")". It returns "0". If, on
the other hand I change the formula to "=COUNTIF(A1:A7;"1000")" it returns
"4". So clearly the wildcard is at fault, but I thought this was exactly how
they were supposed to work. I've also tried "=COUNTIF(A1:A7;"1???") but that
also only returns 0. Since everybody tells me wildcards should work fine
here, I must be doing something pretty basic wrong - and ideas what it might
be?

COUNTIF with wildcards
 

But wildcards are for text and your data is numeric
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Babymech" wrote in message
...
This question is bugging me to no end, primarily because the answer is
easily
available - every resource I've found online tells me that countif works
with
wildcards, but for the life of me I can't get it to work. I must be doing
something wrong with the syntax, but I have no idea what. Basically I want
to
find out how many cells in a range contain values between 1000 and 1999. I
can solve it with a "greater than, smaller than" combination, but it seems
somewhat more elegant to use a wildcard... only it doesn't work. This is
what
I have:

In column A, I have the following values:

A1 1000
A2 1200
A3 1100
A4 1000
A5 1000
A6 1000
A7 1200

And in B1 I have the formula "=COUNTIF(A1:A7;"1*")". It returns "0". If,
on
the other hand I change the formula to "=COUNTIF(A1:A7;"1000")" it returns
"4". So clearly the wildcard is at fault, but I thought this was exactly
how
they were supposed to work. I've also tried "=COUNTIF(A1:A7;"1???") but
that
also only returns 0. Since everybody tells me wildcards should work fine
here, I must be doing something pretty basic wrong - and ideas what it
might
be?

COUNTIF with wildcards
 

Wildcars are for text, you have to do it like this

=COUNTIF(A1:A11,"1000")-COUNTIF(A1:A11,""1999)

Mike

"Babymech" wrote:

This question is bugging me to no end, primarily because the answer is easily
available - every resource I've found online tells me that countif works with
wildcards, but for the life of me I can't get it to work. I must be doing
something wrong with the syntax, but I have no idea what. Basically I want to
find out how many cells in a range contain values between 1000 and 1999. I
can solve it with a "greater than, smaller than" combination, but it seems
somewhat more elegant to use a wildcard... only it doesn't work. This is what
I have:

In column A, I have the following values:

A1 1000
A2 1200
A3 1100
A4 1000
A5 1000
A6 1000
A7 1200

And in B1 I have the formula "=COUNTIF(A1:A7;"1*")". It returns "0". If, on
the other hand I change the formula to "=COUNTIF(A1:A7;"1000")" it returns
"4". So clearly the wildcard is at fault, but I thought this was exactly how
they were supposed to work. I've also tried "=COUNTIF(A1:A7;"1???") but that
also only returns 0. Since everybody tells me wildcards should work fine
here, I must be doing something pretty basic wrong - and ideas what it might
be?

COUNTIF with wildcards
 

Well, that explains it... Thanks Bernard, and Mike, for your answers - did it
with SUMPRODUCT instead and it works fine - and I learned something new about
wildcards.

"Bernard Liengme" wrote:

But wildcards are for text and your data is numeric
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Babymech" wrote in message
...
This question is bugging me to no end, primarily because the answer is
easily
available - every resource I've found online tells me that countif works
with
wildcards, but for the life of me I can't get it to work. I must be doing
something wrong with the syntax, but I have no idea what. Basically I want
to
find out how many cells in a range contain values between 1000 and 1999. I
can solve it with a "greater than, smaller than" combination, but it seems
somewhat more elegant to use a wildcard... only it doesn't work. This is
what
I have:

In column A, I have the following values:

A1 1000
A2 1200
A3 1100
A4 1000
A5 1000
A6 1000
A7 1200

And in B1 I have the formula "=COUNTIF(A1:A7;"1*")". It returns "0". If,
on
the other hand I change the formula to "=COUNTIF(A1:A7;"1000")" it returns
"4". So clearly the wildcard is at fault, but I thought this was exactly
how
they were supposed to work. I've also tried "=COUNTIF(A1:A7;"1???") but
that
also only returns 0. Since everybody tells me wildcards should work fine
here, I must be doing something pretty basic wrong - and ideas what it
might
be?