Hello all,

I know how to determine wether a group of characters, or the content of one
cell, is present somewhere among the content of another cell. For instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These cells can
have complex contents, I just want to now how many times the content of C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content of one
cell, is present somewhere among the content of another cell. For instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These cells can
have complex contents, I just want to now how many times the content of C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Or, just change Sum to Sumproduct to allow regular entry.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gary''s Student" wrote in message
...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I
want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These cells
can
have complex contents, I just want to now how many times the content of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I
want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These cells
can
have complex contents, I just want to now how many times the content of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the content
of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Try this:

=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(UPPER(A1:A5),UPPER(C$1),"")))/LEN(C$1
))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I
want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells
can
have complex contents, I just want to now how many times the content of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content
of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the one
Ragdyer posted both will count the word in C1 as being in the text even if
it is embedded within another word. For example, if you were searching for
the word "cat" and one of your cells had "I can concatenate the text" in it,
that would register as having the word "cat" in it because the word "cat" is
in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I
want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These cells
can
have complex contents, I just want to now how many times the content of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

A quick follow up question to Jack Sons. When you said, "I just want to now
how many times the content of C1 is present somewhere in the content of
cells in the range", how did you want to count it if the word in C1 appeared
more than once in a single cell? Does it add 1 or 2 to the total count you
are looking for. I ask because my formula counts it as 1 where as Ragdyer's
formula counts it as 2. I **think** my formula is what you are asking for,
but your introduction containing the Len-Len formula (which count multiple
occurrences as multiple hits) kind of confuses the overall question.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the
one Ragdyer posted both will count the word in C1 as being in the text
even if it is embedded within another word. For example, if you were
searching for the word "cat" and one of your cells had "I can concatenate
the text" in it, that would register as having the word "cat" in it
because the word "cat" is in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and I
want
to know wether and how many times it occurs in cell A1 (possibly amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells can
have complex contents, I just want to now how many times the content of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Hey Rick,
Your suggested formula will *not* count multiple occurrences in the same
cell of the sought after string in C1.

If "catcat" is in A1, your formula returns 1,
where the others return 2.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the
one
Ragdyer posted both will count the word in C1 as being in the text even if
it is embedded within another word. For example, if you were searching for
the word "cat" and one of your cells had "I can concatenate the text" in
it,
that would register as having the word "cat" in it because the word "cat"
is
in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content
of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and
I
want
to know wether and how many times it occurs in cell A1 (possibly
amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells
can
have complex contents, I just want to now how many times the content
of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Sorry Rick,
Didn't see your post b4 I posted my last.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
A quick follow up question to Jack Sons. When you said, "I just want to
now
how many times the content of C1 is present somewhere in the content of
cells in the range", how did you want to count it if the word in C1
appeared
more than once in a single cell? Does it add 1 or 2 to the total count you
are looking for. I ask because my formula counts it as 1 where as
Ragdyer's
formula counts it as 2. I **think** my formula is what you are asking for,
but your introduction containing the Len-Len formula (which count multiple
occurrences as multiple hits) kind of confuses the overall question.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the
one Ragdyer posted both will count the word in C1 as being in the text
even if it is embedded within another word. For example, if you were
searching for the word "cat" and one of your cells had "I can
concatenate
the text" in it, that would register as having the word "cat" in it
because the word "cat" is in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content
of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and
I
want
to know wether and how many times it occurs in cell A1 (possibly
amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells can
have complex contents, I just want to now how many times the content
of
C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Yes, you and I read the OP's question differently. His question really
didn't state clearly (at least to me) what he wanted to do. I guess we will
have to wait for him to come back to the thread and let us know what he
actually wanted.

Rick


"Ragdyer" wrote in message
...
Sorry Rick,
Didn't see your post b4 I posted my last.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
A quick follow up question to Jack Sons. When you said, "I just want to
now
how many times the content of C1 is present somewhere in the content of
cells in the range", how did you want to count it if the word in C1
appeared
more than once in a single cell? Does it add 1 or 2 to the total count
you
are looking for. I ask because my formula counts it as 1 where as
Ragdyer's
formula counts it as 2. I **think** my formula is what you are asking
for,
but your introduction containing the Len-Len formula (which count
multiple
occurrences as multiple hits) kind of confuses the overall question.

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the
one Ragdyer posted both will count the word in C1 as being in the text
even if it is embedded within another word. For example, if you were
searching for the word "cat" and one of your cells had "I can
concatenate
the text" in it, that would register as having the word "cat" in it
because the word "cat" is in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a
work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content
of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1
and
I
want
to know wether and how many times it occurs in cell A1 (possibly
amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells can
have complex contents, I just want to now how many times the content
of
C1
is present somewhere in the content of cells in the range. And as
een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

He did say that Gary's formula "worked like a charm"!<bg

I only interjected to mention (belatedly) that Sumproduct() made arrays
unnecessary, and to also revise Gary's formula to *not* be case sensitive.

BTW ... to kick a dead horse <bg ... 1 less function call:

=SUMPRODUCT(--ISNUMBER(SEARCH(C1,A1:A5)))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, you and I read the OP's question differently. His question really
didn't state clearly (at least to me) what he wanted to do. I guess we
will
have to wait for him to come back to the thread and let us know what he
actually wanted.

Rick


"Ragdyer" wrote in message
...
Sorry Rick,
Didn't see your post b4 I posted my last.
--
Regards,

RD


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

--------------------------------------------------------------------------
-
"Rick Rothstein (MVP - VB)" wrote
in
message ...
A quick follow up question to Jack Sons. When you said, "I just want to
now
how many times the content of C1 is present somewhere in the content of
cells in the range", how did you want to count it if the word in C1
appeared
more than once in a single cell? Does it add 1 or 2 to the total count
you
are looking for. I ask because my formula counts it as 1 where as
Ragdyer's
formula counts it as 2. I **think** my formula is what you are asking
for,
but your introduction containing the Len-Len formula (which count
multiple
occurrences as multiple hits) kind of confuses the overall question.

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as
the
one Ragdyer posted both will count the word in C1 as being in the
text
even if it is embedded within another word. For example, if you were
searching for the word "cat" and one of your cells had "I can
concatenate
the text" in it, that would register as having the word "cat" in it
because the word "cat" is in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a
work
around?

Jack.

"Gary''s Student" schreef
in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER
key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the
content
of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1
and
I
want
to know wether and how many times it occurs in cell A1 (possibly
amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells can
have complex contents, I just want to now how many times the
content
of
C1
is present somewhere in the content of cells in the range. And as
een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Rick,

You are right. The number of occurences I am interested in is indeed the
number of cells that contain the reference group of characters. Even more
so, I would like to get "as output" also a list of the adresses of those
cells (my range is not necessarely one dimensional). Do you know how?

Jack.


"Rick Rothstein (MVP - VB)" schreef in
bericht ...
A quick follow up question to Jack Sons. When you said, "I just want to now
how many times the content of C1 is present somewhere in the content of
cells in the range", how did you want to count it if the word in C1
appeared more than once in a single cell? Does it add 1 or 2 to the total
count you are looking for. I ask because my formula counts it as 1 where as
Ragdyer's formula counts it as 2. I **think** my formula is what you are
asking for, but your introduction containing the Len-Len formula (which
count multiple occurrences as multiple hits) kind of confuses the overall
question.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as the
one Ragdyer posted both will count the word in C1 as being in the text
even if it is embedded within another word. For example, if you were
searching for the word "cat" and one of your cells had "I can concatenate
the text" in it, that would register as having the word "cat" in it
because the word "cat" is in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a work
around?

Jack.

"Gary''s Student" schreef in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the content
of one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1 and
I want
to know wether and how many times it occurs in cell A1 (possibly
amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25. These
cells can
have complex contents, I just want to now how many times the content
of C1
is present somewhere in the content of cells in the range. And as een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

Thanks RD, very nice. See also the answer I just posted to Ricks earlier
post.

Jack.

"Ragdyer" schreef in bericht
...
He did say that Gary's formula "worked like a charm"!<bg

I only interjected to mention (belatedly) that Sumproduct() made arrays
unnecessary, and to also revise Gary's formula to *not* be case sensitive.

BTW ... to kick a dead horse <bg ... 1 less function call:

=SUMPRODUCT(--ISNUMBER(SEARCH(C1,A1:A5)))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, you and I read the OP's question differently. His question really
didn't state clearly (at least to me) what he wanted to do. I guess we
will
have to wait for him to come back to the thread and let us know what he
actually wanted.

Rick


"Ragdyer" wrote in message
...
Sorry Rick,
Didn't see your post b4 I posted my last.
--
Regards,

RD


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

--------------------------------------------------------------------------
-
"Rick Rothstein (MVP - VB)" wrote
in
message ...
A quick follow up question to Jack Sons. When you said, "I just want
to
now
how many times the content of C1 is present somewhere in the content
of
cells in the range", how did you want to count it if the word in C1
appeared
more than once in a single cell? Does it add 1 or 2 to the total count
you
are looking for. I ask because my formula counts it as 1 where as
Ragdyer's
formula counts it as 2. I **think** my formula is what you are asking
for,
but your introduction containing the Len-Len formula (which count
multiple
occurrences as multiple hits) kind of confuses the overall question.

Rick


"Rick Rothstein (MVP - VB)"
wrote
in
message ...
Here is a slightly shorter, alternate formula (that is also not case
sensitive)...

=SUMPRODUCT(--NOT(ISERR(SEARCH(C$1,A1:A1000))))

By the way, in case you are not aware, the above formula as well as
the
one Ragdyer posted both will count the word in C1 as being in the
text
even if it is embedded within another word. For example, if you were
searching for the word "cat" and one of your cells had "I can
concatenate
the text" in it, that would register as having the word "cat" in it
because the word "cat" is in the middle of the word "concatenate".

Rick


"Jack Sons" wrote in message
...
GS,

Thanks, works like a charm.

With sumproduct it even appears unnecessary to array enter.

But I discovered that these formulae are case sensitve. Is there a
work
around?

Jack.

"Gary''s Student" schreef
in
bericht ...
Try the array formula:

=SUM((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,C$1,"")))/LEN(C$1))

It is entered with a CNTRL-SHFT-ENTER rather than just the ENTER
key.
For
example, if A1 thru A5 contains:

bphdsjd
6rtwbpfsfdbp
123ewbp
mjuobp
few


and C1 contains:

bp

then the fomula will return 5.
--
Gary''s Student - gsnu2007g


"Jack Sons" wrote:

Hello all,

I know how to determine wether a group of characters, or the
content
of
one
cell, is present somewhere among the content of another cell. For
instance
as follows:

If the group of characters I am searching for is in, say, cell C1
and
I
want
to know wether and how many times it occurs in cell A1 (possibly
amidst
other content), I use

=(LEN(A1)-LEN(SUBSTITUTE(A1,C1,"")))/LEN(C1)

But now I want to look into a range of cells, say A1 to A25.
These
cells can
have complex contents, I just want to now how many times the
content
of
C1
is present somewhere in the content of cells in the range. And as
een
option: plus a list of the adresses of the cells that contain the
content of
C1.

Your assistance will be appreciated.

Jack Sons
The Netherlands

See inline...

He did say that Gary's formula "worked like a charm"!<bg

But he may not have tested it with the text in C1 repeated within a single
cell in which case it may have only **looked** like it worked the way he
wanted. And this seems to be the case as I just got a response from the OP
to one of my other messages indicating my reading of the question was
correct.

I only interjected to mention (belatedly) that Sumproduct() made arrays
unnecessary, and to also revise Gary's formula to *not* be case sensitive.

BTW ... to kick a dead horse <bg ... 1 less function call:

=SUMPRODUCT(--ISNUMBER(SEARCH(C1,A1:A5)))

Yes, I keep forgetting the --ISNUMBER in place of NOT(ISERROR) "trick"... I
know it, I just seem to have this mental block against using it for some
reason. Thanks for posting it.

Rick