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