COUNTIF problem
 

greetings, i'm using COUNTIF formula to count responses from a survey and am
having problem as the formula can't distinguish between "N" and "N*"

however, another part of database includes responses "Highest" and
"Highest*" and COUNTIF seemingly can tell the difference here. any ideas?

COUNTIF problem
 

The * is a wildcard character. N* means N followed by anything. Is that how
you want it to work or are you wanting to count the literal string N* ?

To count the literal string N* :

=COUNTI(A1:A100,"N~*")

The tilde character is a wildcard "escape" character.

--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...
greetings, i'm using COUNTIF formula to count responses from a survey and
am
having problem as the formula can't distinguish between "N" and "N*"

however, another part of database includes responses "Highest" and
"Highest*" and COUNTIF seemingly can tell the difference here. any ideas?

COUNTIF problem
 

hi biff, appreciate quick reply, thank you

COUNTIF problem
 

You're welcome!

I guess you figured out my typo? I forgot the "F" in COUNTIF!

=COUNTI(A1:A100,"N~*")

Should be:

=COUNTIF(A1:A100,"N~*")


--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...
hi biff, appreciate quick reply, thank you

COUNTIF problem
 

hi biff, didn't even notice the typo as we probably won't be tweaking
formula for solution (but N~* to identify literal "N*" is great stuff and,
i'm sure, will come in handy. got the gist of the problem once i read your
reply and since our process is pretty formalized, we can ensure, for
instance, that a space precedes any asterisk. COUNTIF easily recognizes that
difference, thanks for the expertise!

COUNTIF problem
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...

hi biff, didn't even notice the typo as we probably won't be tweaking
formula for solution (but N~* to identify literal "N*" is great stuff and,
i'm sure, will come in handy. got the gist of the problem once i read
your
reply and since our process is pretty formalized, we can ensure, for
instance, that a space precedes any asterisk. COUNTIF easily recognizes
that
difference, thanks for the expertise!

COUNTIF problem
 

hi biff, hoping this reply gets to you. i've got another problem with
COUNTIF, seems to have hard time seeing difference between text like, =BBB
and =AA, or =AA 3% and AA10%, or <BBB and 15%; Non-USD: P. do the < or
signs have any significance?

COUNTIF problem
 

Try it like this:

=COUNTIF(A1:A10,"==BBB")

Notice the additional = sign.

That might be a little cryptic and induce confusion. Here's another way:

=SUMPRODUCT(--(A1:A10="=BBB"))

That's "slightly" more intuitive!

--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...

hi biff, hoping this reply gets to you. i've got another problem with
COUNTIF, seems to have hard time seeing difference between text like,
=BBB
and =AA, or =AA 3% and AA10%, or <BBB and 15%; Non-USD: P. do the < or

signs have any significance?

COUNTIF problem
 

hi biff, thanks for the reply! fyi, for COUNTIF, and later SUMPRODUCT, i
was using cell reference rather than something explicit, in quotes, for the
criteria and i'm thinking that's part of the problem.

hope this makes some sense: in the range (a1:a10) i have a menu generated
list which may have zero, one, or multiple entries. the criteria is each
cell in a 50+ column X 200+ row grid and i just wanted a 1 to result if a
cell in the gird matches any of the choices macro user selected from a menu.
the value 1 is more like a marker that helps macro figure which columns and
rows to print (utilizing autofilter).

as a result i'm using IF(ISNA(MATCH(a1:a10, mycell, 0)),0,1) because MATCH
seems to work well with labels, which is what all the data is/will be
formatted in.

COUNTIF problem
 

Oh, I get it! Sometimes I'm pretty dense!

When using a cell to hold the criteria:

AA1 = 10

=COUNTIF(A1:A10,"="&AA1) = greater than or equal to AA1
=COUNTIF(A1:A10,""&AA1) = greater than AA1
=COUNTIF(A1:A10,AA1) = equals AA1
=COUNTIF(A1:A10,"<"&AA1) = is not equal to AA1
=COUNTIF(A1:A10,"<="&AA1) = less than or equal to AA1
=COUNTIF(A1:A10,"<"&AA1) = less than AA1

--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...

hi biff, thanks for the reply! fyi, for COUNTIF, and later SUMPRODUCT, i
was using cell reference rather than something explicit, in quotes, for
the
criteria and i'm thinking that's part of the problem.

hope this makes some sense: in the range (a1:a10) i have a menu generated
list which may have zero, one, or multiple entries. the criteria is each
cell in a 50+ column X 200+ row grid and i just wanted a 1 to result if a
cell in the gird matches any of the choices macro user selected from a
menu.
the value 1 is more like a marker that helps macro figure which columns
and
rows to print (utilizing autofilter).

as a result i'm using IF(ISNA(MATCH(a1:a10, mycell, 0)),0,1) because MATCH
seems to work well with labels, which is what all the data is/will be
formatted in.

COUNTIF problem
 

hi biff, i've sent you copy of the model, and forgot to include that to
prompt macro hit [ctrl-a]

COUNTIF problem
 

hi biff, sent file to your biffinpitt address but got bounced. if you want
to contact me i can be reached at below address


Replace mailinator with mwamllc

COUNTIF problem
 

Well, I don't accept files unless I've *invited* someone to send it.

If I invite someone to send a file I'll reject it for security reasons if it
contains any VBA code.

--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...

hi biff, sent file to your biffinpitt address but got bounced. if you
want
to contact me i can be reached at below address


Replace mailinator with mwamllc

COUNTIF problem
 

hi biff, understood; have a great weekend!