I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!

On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy
wrote:

I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!


=COUNTIF(A:A,"*abc*")

or, if your string to search for is in a cell:

=COUNTIF(A:A,"*"&D2&"*")


--ron

Thanks Ron,

I'd managed to get that far but the trouble is that I need the cell to be
counted if there is "abc" OR "def" within it. It's the OR bit which is
causing me trouble.

Any ideas?

Tom

"Ron Rosenfeld" wrote:

On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy
wrote:

I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!


=COUNTIF(A:A,"*abc*")

or, if your string to search for is in a cell:

=COUNTIF(A:A,"*"&D2&"*")


--ron

Tommy T Wrote:
Thanks Ron,

I'd managed to get that far but the trouble is that I need the cell to
be
counted if there is "abc" OR "def" within it. It's the OR bit which is
causing me trouble.

Any ideas?

Hi Tommy

Try this =SUM((COUNTIF(A:A,"*abc*")+(COUNTIF(A:A,"*def*"))) )


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=401899

On Mon, 5 Sep 2005 04:46:01 -0700, "Tommy T"
wrote:

Thanks Ron,

I'd managed to get that far but the trouble is that I need the cell to be
counted if there is "abc" OR "def" within it. It's the OR bit which is
causing me trouble.

Any ideas?

Tom

"Ron Rosenfeld" wrote:

On Mon, 5 Sep 2005 04:01:02 -0700, "Tommy T" <Tommy
wrote:

I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!


=COUNTIF(A:A,"*abc*")

or, if your string to search for is in a cell:

=COUNTIF(A:A,"*"&D2&"*")


--ron


=SUM(COUNTIF(A:A,{"*abc*","*def*"}))

will count each cell that has one or the other string. However, it will double
count cells that have both strings: e.g. 12abc34def56 would get counted
twice.

If you don't want double counting, then:

=SUM(COUNTIF(A:A,{"*abc*","*def*"}),-COUNTIF(A:A,"*abc*def*"),-COUNTIF(A:A,"*def*abc*"))




--ron

Tommy T Wrote:
I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me
crazy,
please help!

Hi Tommy

Try this =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your
data, change abc to def to count the other option


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=401899

Thanks Paul,

Trouble is that some of the cells contain both phrases, if I have two
different countif functions then they get double counted which I'm trying to
avoid.

Tom

"Paul Sheppard" wrote:


Tommy T Wrote:
I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me
crazy,
please help!

Hi Tommy

Try this =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit your
data, change abc to def to count the other option


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=401899

Tommy T Wrote:
Thanks Paul,

Trouble is that some of the cells contain both phrases, if I have two
different countif functions then they get double counted which I'm
trying to
avoid.

Tom

"Paul Sheppard" wrote:


Tommy T Wrote:
I have a colomn of alpha-numeric strings and need to count cells
which
contain phrase A or phrase B buried within them. This is driving
me
crazy,
please help!

Hi Tommy

Try this =COUNTIF(A1:A2,"*abc*") change the range A1:A2 to suit
your
data, change abc to def to count the other option


--
Paul Sheppard



------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread:
http://www.excelforum.com/showthread...hreadid=401899



Hi Tommy

Try Data Filter Autofilter Custom filter contains abc or
contains def


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=401899

Try...

=SUMPRODUCT(--(ISNUMBER(SEARCH("abc",A1:A100))+ISNUMBER(SEARCH(" def",A1:A
100))0))

or

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,A1:A100))+ISNUMBER(SEARCH(C1,A 1:A100))
0))

....where B1 contains your first criterion, such as abc, and C1 contains
your second criterion, such as def.

Hope this helps!

In article ,
"Tommy T" <Tommy wrote:

I have a colomn of alpha-numeric strings and need to count cells which
contain phrase A or phrase B buried within them. This is driving me crazy,
please help!