[cjlatta]

(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or ",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. I will try to show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. Can I do this?
Any help greatly appreciated.

[RagDyeR]

Does this work for you:

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

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"cjlatta" wrote in message
...
(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or
",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the
cell
contains "abcd" and "abcf" no matter what order it's in. I will try to
show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1
or
4. Can I do this?
Any help greatly appreciated.

[robzrob]

On Jul 11, 11:20*pm, cjlatta
wrote:
(Using Excel2003/WinXP) *I am trying to count the number of times 2 values
are in a cell. *The string may contain the values ",abcd, abcf," or ",abcf,
abcd," *The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). *I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. *I will try to show
an example below:

* Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. *Can I do this?
Any help greatly appreciated.

If you don't mind having another column and assuming the 'words' are
in B1, B2, etc, in C1 put =FIND("abcd",B1)*FIND("abcf",B1) and copy
down. Then at the bottom of column C, assuming 10 rows, put
=COUNTIF(C1:C10,"0").

[Ron Rosenfeld]

On Fri, 11 Jul 2008 15:20:01 -0700, cjlatta
wrote:

(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or ",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. I will try to show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. Can I do this?
Any help greatly appreciated.

If all the values are four characters, and/or there is no chance the string
being searched for could be found within another string (e.g. if 3, tabcdx, yz,
abcf is not a possibility) then:

=SUMPRODUCT(--ISNUMBER(SEARCH({"*abcd*abcf","*abcf*abcd"},A1:A5) ))
--ron

[Rick Rothstein \(MVP - VB\)[_887_]]

Why not this...

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

and then it won't matter if "abcd, abcg, abcf" is in there?

Rick


"RagDyer" wrote in message
...
Does this work for you:

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

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"cjlatta" wrote in message
...
(Using Excel2003/WinXP) I am trying to count the number of times 2
values
are in a cell. The string may contain the values ",abcd, abcf," or
",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the
cell
contains "abcd" and "abcf" no matter what order it's in. I will try to
show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1
or
4. Can I do this?
Any help greatly appreciated.

[Ron Rosenfeld]

On Fri, 11 Jul 2008 16:02:19 -0700, "RagDyer" wrote:

Does this work for you:

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

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

--
HTH,

RD

Pasting in the OP's data, your formula returns a count of 2. The OP wanted a
count of 3.
--ron

[RagDyeR]

Why not?
I'll tell you why not.
I didn't think about it!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
Why not this...

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

and then it won't matter if "abcd, abcg, abcf" is in there?

Rick


"RagDyer" wrote in message
...
Does this work for you:

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

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"cjlatta" wrote in message
...
(Using Excel2003/WinXP) I am trying to count the number of times 2
values
are in a cell. The string may contain the values ",abcd, abcf," or
",abcf,
abcd," The other part that's stumping me is the string could be
",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the
cell
contains "abcd" and "abcf" no matter what order it's in. I will try to
show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not
1
or
4. Can I do this?
Any help greatly appreciated.

[RagDyeR]

Yeah!

I see my typo.

Left out a space between the 2 words in the 2nd half of the array constant.

Thanks.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Rosenfeld" wrote in message
...
On Fri, 11 Jul 2008 16:02:19 -0700, "RagDyer"
wrote:

Does this work for you:

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

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

--
HTH,

RD

Pasting in the OP's data, your formula returns a count of 2. The OP
wanted a
count of 3.
--ron

[cjlatta]

Unfortunately, not all the values are four characters. Like the suggestion
though!
Thanks for the info.

"Ron Rosenfeld" wrote:

On Fri, 11 Jul 2008 15:20:01 -0700, cjlatta
wrote:

(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or ",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. I will try to show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. Can I do this?
Any help greatly appreciated.

If all the values are four characters, and/or there is no chance the string
being searched for could be found within another string (e.g. if 3, tabcdx, yz,
abcf is not a possibility) then:

=SUMPRODUCT(--ISNUMBER(SEARCH({"*abcd*abcf","*abcf*abcd"},A1:A5) ))
--ron

[cjlatta]

Thank you! This will do it for me, especially since the values can be in any
part of the sequence!


"Rick Rothstein (MVP - VB)" wrote:

Why not this...

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

and then it won't matter if "abcd, abcg, abcf" is in there?

Rick


"RagDyer" wrote in message
...
Does this work for you:

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

Assuming there is *no* possibility of there being:

"abcd, abcg, abcf"

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"cjlatta" wrote in message
...
(Using Excel2003/WinXP) I am trying to count the number of times 2
values
are in a cell. The string may contain the values ",abcd, abcf," or
",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the
cell
contains "abcd" and "abcf" no matter what order it's in. I will try to
show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1
or
4. Can I do this?
Any help greatly appreciated.

[cjlatta]

This looks really good to for another part of this problem. However, I can't
figure out how to do the "double find." When I enter the formula as written,
I get a Value! error. Do I need an extra set of parenthesis? Using your
example, I'm assuming that in C1, the result would be 1 (using the values I
have listed in my example), in C2 the result would be 2, in C3 the result
would be 2, in C4 the result would be 1 and in C5 the result would be 2?

Thanks!

"robzrob" wrote:

On Jul 11, 11:20 pm, cjlatta
wrote:
(Using Excel2003/WinXP) I am trying to count the number of times 2 values
are in a cell. The string may contain the values ",abcd, abcf," or ",abcf,
abcd," The other part that's stumping me is the string could be ",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the cell
contains "abcd" and "abcf" no matter what order it's in. I will try to show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not 1 or
4. Can I do this?
Any help greatly appreciated.

If you don't mind having another column and assuming the 'words' are
in B1, B2, etc, in C1 put =FIND("abcd",B1)*FIND("abcf",B1) and copy
down. Then at the bottom of column C, assuming 10 rows, put
=COUNTIF(C1:C10,"0").

[Rick Rothstein \(MVP - VB\)[_905_]]

The error is on purpose. FIND will return a value only if what it is
searching for exists, otherwise it returns an error. The COUNTIF function,
as set up, will only count values greater than zero... and error condition
is not a value greater than zero and hence won't be counted. This means that
the COUNTIF statement will only count cases where both FIND statements find
something and, since the order of multiplicands in multiplication doesn't
matter, the count will occur no matter what the order of the two strings
being searched for are in within the text.

Rick


"cjlatta" wrote in message
...
This looks really good to for another part of this problem. However, I
can't
figure out how to do the "double find." When I enter the formula as
written,
I get a Value! error. Do I need an extra set of parenthesis? Using your
example, I'm assuming that in C1, the result would be 1 (using the values
I
have listed in my example), in C2 the result would be 2, in C3 the result
would be 2, in C4 the result would be 1 and in C5 the result would be 2?

Thanks!

"robzrob" wrote:

On Jul 11, 11:20 pm, cjlatta
wrote:
(Using Excel2003/WinXP) I am trying to count the number of times 2
values
are in a cell. The string may contain the values ",abcd, abcf," or
",abcf,
abcd," The other part that's stumping me is the string could be
",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the
cell
contains "abcd" and "abcf" no matter what order it's in. I will try to
show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not
1 or
4. Can I do this?
Any help greatly appreciated.

If you don't mind having another column and assuming the 'words' are
in B1, B2, etc, in C1 put =FIND("abcd",B1)*FIND("abcf",B1) and copy
down. Then at the bottom of column C, assuming 10 rows, put
=COUNTIF(C1:C10,"0").

[cjlatta]

Thanks for the clarification. I'm using many of these tips for this
particular spreadsheet - thanks for everyone's help.


"Rick Rothstein (MVP - VB)" wrote:

The error is on purpose. FIND will return a value only if what it is
searching for exists, otherwise it returns an error. The COUNTIF function,
as set up, will only count values greater than zero... and error condition
is not a value greater than zero and hence won't be counted. This means that
the COUNTIF statement will only count cases where both FIND statements find
something and, since the order of multiplicands in multiplication doesn't
matter, the count will occur no matter what the order of the two strings
being searched for are in within the text.

Rick


"cjlatta" wrote in message
...
This looks really good to for another part of this problem. However, I
can't
figure out how to do the "double find." When I enter the formula as
written,
I get a Value! error. Do I need an extra set of parenthesis? Using your
example, I'm assuming that in C1, the result would be 1 (using the values
I
have listed in my example), in C2 the result would be 2, in C3 the result
would be 2, in C4 the result would be 1 and in C5 the result would be 2?

Thanks!

"robzrob" wrote:

On Jul 11, 11:20 pm, cjlatta
wrote:
(Using Excel2003/WinXP) I am trying to count the number of times 2
values
are in a cell. The string may contain the values ",abcd, abcf," or
",abcf,
abcd," The other part that's stumping me is the string could be
",abcd,
abcf, abcg," (or longer). I am wanting to know the number of times the
cell
contains "abcd" and "abcf" no matter what order it's in. I will try to
show
an example below:

Values
1,abcd,
2 ,abcd, abcf,
3 ,abcd, abcf, abcg,
4 ,abcd, abcg,
5 ,abcf, abcd,

I would want to include cell 2, cell 3, and cell 5 in my count, but not
1 or
4. Can I do this?
Any help greatly appreciated.

If you don't mind having another column and assuming the 'words' are
in B1, B2, etc, in C1 put =FIND("abcd",B1)*FIND("abcf",B1) and copy
down. Then at the bottom of column C, assuming 10 rows, put
=COUNTIF(C1:C10,"0").

[Ron Rosenfeld]

On Mon, 14 Jul 2008 07:15:00 -0700, cjlatta
wrote:

Unfortunately, not all the values are four characters. Like the suggestion
though!
Thanks for the info.

What I meant to write is that the technique of using wild cards (or FIND or
SEARCH) will not differentiate: abcd from abcde (or from any string in which
abcd is a substring.

If this is an issue, you can use a UDF such as:

============================
Option Explicit
Function StringCount(rg As Range, str1 As String, str2 As String) As Double
Dim c As Range
Dim re As Object, mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\b" & str1 & "\b.*\b" & str2 & _
"\b)|(\b" & str2 & "\b.*\b" & str1 & "\b)"

For Each c In rg
If re.test(c.Value) = True Then _
StringCount = StringCount + 1
Next c
End Function
=================================

To enter this UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code above into the window that opens.

To use this, enter a formula of the type:

=StringCount(A1:A10,"abcd","abcf")

The UDF requires that the two substrings be present (in any order) and it will
not count, for example, abcde when looking for abcd.
--ron

[Harlan Grove[_2_]]

"Rick Rothstein \(MVP - VB\)" wrote...
Why not this...

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

Maybe in this particular case, but if these cells could contain values
like "abcde,abcFOOBAR" your formula would include them in the count
when they shouldn't be.

[Rick Rothstein \(MVP - VB\)[_910_]]

That's true; but, of course, this is a problem for most search type
functions (FIND, SEARCH, InStr in VB, etc.). Of course, permitting these
items anywhere in the text seemed to be indicated given this example that
the OP included...

3 ,abcd, abcf, abcg,

where his inclusion of "abcg" (ignoring the commas) would seem to be your
FOOBAR case. I guess an argument could be made that at least the first one
might have to be located at the start of the text; but that were the
requirement, it could be covered by omitting the leading asterisk. in both
array strings.

Rick


"Harlan Grove" wrote in message
...
"Rick Rothstein \(MVP - VB\)" wrote...
Why not this...

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

Maybe in this particular case, but if these cells could contain values
like "abcde,abcFOOBAR" your formula would include them in the count
when they shouldn't be.

[Harlan Grove[_2_]]

"Rick Rothstein \(MVP - VB\)" wrote...
That's true; but, of course, this is a problem for most search type
functions (FIND, SEARCH, InStr in VB, etc.). Of course, permitting
these items anywhere in the text seemed to be indicated given this
example that the OP included...

3 ,abcd, abcf, abcg,
....

Note the commas.

*YOUR* formula fails to make use of the commas.

Try the array formula

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

[Rick Rothstein \(MVP - VB\)[_911_]]

I guess I am missing your point. 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), so it seemed to me that not factoring
them into the search was the thing to do. Are you suggesting the OP may have
been looking for something else that I am simply not seeing?

Rick


"Harlan Grove" wrote in message
...
"Rick Rothstein \(MVP - VB\)" wrote...
That's true; but, of course, this is a problem for most search type
functions (FIND, SEARCH, InStr in VB, etc.). Of course, permitting
these items anywhere in the text seemed to be indicated given this
example that the OP included...

3 ,abcd, abcf, abcg,
...

Note the commas.

*YOUR* formula fails to make use of the commas.

Try the array formula

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

[Harlan Grove[_2_]]

"Rick Rothstein \(MVP - VB\)" wrote...
I guess I am missing your point. . . .

Clearly.

. . . 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.

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?

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

=SUMPRODUCT(--ISNUMBER(
FIND(",abcd,",SUBSTITUTE(","&A1:A5&","," ",""))
+FIND(",abcf,",SUBSTITUTE(","&A1:A5&","," ",""))
))

[Rick Rothstein \(MVP - VB\)[_917_]]

. . . 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

[Ron Rosenfeld]

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

[Rick Rothstein \(MVP - VB\)[_918_]]

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

[Rick Rothstein \(MVP - VB\)[_919_]]

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