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