how to count if cell "contains" a word

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

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

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.

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

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.

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

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

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.