I am trying to calculate a series of text occurences within a spread
sheet. I cannot seem to figure out a formula to calculate this. I
found this:

=SUM(LEN(A8:A13)-LEN(SUBSTITUTE(A8:A13,"apple","")))/LEN("apple")

But this only works for mac. I would like the same result, but on a
PC.

I throw myself to the mercy of Google Groups!

It is an array formula.
Select the cell with the formula, click in the formula bar then press Ctrl + Shift + Enter
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"ibo4lyf"
wrote in message
I am trying to calculate a series of text occurences within a spread
sheet. I cannot seem to figure out a formula to calculate this. I
found this:

=SUM(LEN(A8:A13)-LEN(SUBSTITUTE(A8:A13,"apple","")))/LEN("apple")

But this only works for mac. I would like the same result, but on a
PC.
I throw myself to the mercy of Google Groups!

This formula will work on the PC, too.

Remember...

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And you could use:

=SUMproduct(LEN(A8:A13)-LEN(SUBSTITUTE(A8:A13,"apple","")))/LEN("apple")
(and not have to enter it as an array formula)

Remember that =substitute() is case sensitive. APPLE, Apple, ApPlE won't be
found.

=SUMproduct(LEN(A8:A13)-LEN(SUBSTITUTE(lower(A8:A13),"apple","")))/LEN("apple")

If you don't care about case.

ibo4lyf wrote:
>
> I am trying to calculate a series of text occurences within a spread
> sheet. I cannot seem to figure out a formula to calculate this. I
> found this:
>
> =SUM(LEN(A8:A13)-LEN(SUBSTITUTE(A8:A13,"apple","")))/LEN("apple")
>
> But this only works for mac. I would like the same result, but on a
> PC.
>
> I throw myself to the mercy of Google Groups!

--

Dave Peterson

Thank you very much Jim and Dave. Lifesavers!