how to count if cell "contains" a word

On Tue, 15 Jul 2008 03:16:16 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Okay, I think this is where we are looking at the problem differently. I
read into the OP's examples that his delimited text were **all** 4
characters long. For that interpretation, the commas cannot affect the
search as there would be no way to get a false positive under that
condition.

Note this response of the OP to an effort of mine in which I posed that
question:

Unfortunately, not all the values are four characters.
--ron

Okay, I think this is where we are looking at the problem differently. I
read into the OP's examples that his delimited text were **all** 4
characters long. For that interpretation, the commas cannot affect the
search as there would be no way to get a false positive under that
condition.

Note this response of the OP to an effort of mine in which I posed that
question:

Unfortunately, not all the values are four characters.

I had not read that response (which he posted earlier in the day that Harlan
responded to me), so I was still laboring under the impression I had formed
from my first reading of the original post when I responded to Harlan (which
I think is obvious from the arguments I used in my responses to Harlan).
Thanks for pointing that out.

Rick

Given Ron's posting to my last message to you, please disregard my last
several postings. It appears you were aware of the OP's posting to Ron
mentioning that the items were not restricted to 4-character each and,
having not read that message, I was not.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
. . . The way I read the initial post, the OP was interested in
finding the 4-character sequences without regard to the commas
(I assumed they were list delimiters), . . .

Yes, it's PRECISELY because they'd be delimiters that it's A BIG
MISTAKE to discard them.

. . . so it seemed to me that not factoring them into the search
was the thing to do. . . .

Duh. Delimiters are usually CRITICAL. They're what allow for
distinguishing abcf a separate token sought from abcFUBAR a token not
necessarily being sought.

Okay, I think this is where we are looking at the problem differently. I
read into the OP's examples that his delimited text were **all** 4
characters long. For that interpretation, the commas cannot affect the
search as there would be no way to get a false positive under that
condition.


Regardless, your formula

=SUM(COUNTIF(A1:A5,{"*abcd*abcf*","*abcf*abcd*"}))

1. does too much work - if order is unimportant, then it's sufficient
to search for abcd and abcf, either as delimited tokens or simple
substrings, it's unnecessary to search for one then the other and the
other then the first;
2. is a bug in waiting - try your formula on the singe cell

0 ,abcx,abcd,abcf,abcd,xyz

In a single cell, should this be counted as 1 or 2?

I don't know... in re-reading the original posting I am not totally sure;
however, I still lean to the OP wanting to count lines of occurrences as
opposed to total occurrence. And, I would point out that the OP did seem
satisfied with the results of my formula (actually, technically it isn't
"mine" as I only raised a question regarding making a minor change to the
formula RagDyer posted), so that may be an answer in itself.

Compare this to the results of my formula,

=COUNT(FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ",""))
+FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ","")))

If you don't like it as an array formula, make it

I have no problem with array formulas. You raise a good point, although
using my interpretation of the OP's setup, I would consider this
modification of your formula to answer his request...

=COUNT(FIND("abcd",A1:A5)+FIND("abcf",A1:A5))

Rick