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

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.

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.

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.

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.

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

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.

"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&","," ","")))

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

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

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

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

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

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

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

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

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