Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF and wildcards | Excel Discussion (Misc queries) | |||
Countif wildcards & 2 criteria | Excel Discussion (Misc queries) | |||
COUNTIF with wildcards | Excel Worksheet Functions | |||
countif wildcards? | Excel Discussion (Misc queries) | |||
wildcards in vba | Excel Discussion (Misc queries) |