COUNTIF problem

[mwam423]

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?

 

[T. Valko]

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.

 

[mwam423]

hi biff, appreciate quick reply, thank you

 

[T. Valko]

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~*")

 

[mwam423]

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!

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[mwam423]

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?

 

[T. Valko]

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!

 

[mwam423]

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.

 

[T. Valko]

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

 

[mwam423]

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

 

[mwam423]

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

(e-mail address removed)
Replace mailinator with mwamllc

 

[T. Valko]

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.

 

[mwam423]

hi biff, understood; have a great weekend!