Hi,
Lets say I want to look for the letter "O" in a cell with the text "How are
you?" and count the number of "O" in that particular cell, the formula would
be something like:
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O",""))

and the result for "How are you?" will return 2.

My question is, what if I want to do it for multiple cells? For instance,
from Cell A1 to A10. Thanks in advance.
--
Shawn Toh (tohlz)
Microsoft MVP PowerPoint

(Amazing PowerPoint animations, artworks, games here)
http://pptheaven.mvps.org
PowerPoint Heaven - The Power to Animate

Hi,

you could use an array formula. Use CTRL+SHIFT+ENTER to commit the formula
rather than the usual ENTER.

=SUM(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O","")))

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"tohlz" <pptheaven[AT]gmail[DOT]com> wrote in message
news:875FF5DA-72FA-450E-B8D2-(E-Mail Removed)...
> Hi,
> Lets say I want to look for the letter "O" in a cell with the text "How
> are
> you?" and count the number of "O" in that particular cell, the formula
> would
> be something like:
> =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O",""))
>
> and the result for "How are you?" will return 2.
>
> My question is, what if I want to do it for multiple cells? For instance,
> from Cell A1 to A10. Thanks in advance.
> --
> Shawn Toh (tohlz)
> Microsoft MVP PowerPoint
>
> (Amazing PowerPoint animations, artworks, games here)
> http://pptheaven.mvps.org
> PowerPoint Heaven - The Power to Animate

Try

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O","")))

Mike

"tohlz" wrote:

> Hi,
> Lets say I want to look for the letter "O" in a cell with the text "How are
> you?" and count the number of "O" in that particular cell, the formula would
> be something like:
> =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O",""))
>
> and the result for "How are you?" will return 2.
>
> My question is, what if I want to do it for multiple cells? For instance,
> from Cell A1 to A10. Thanks in advance.
> --
> Shawn Toh (tohlz)
> Microsoft MVP PowerPoint
>
> (Amazing PowerPoint animations, artworks, games here)
> http://pptheaven.mvps.org
> PowerPoint Heaven - The Power to Animate

Thanks for the help, Andy and Mike.
--
Shawn Toh (tohlz)
Microsoft MVP PowerPoint

(Amazing PowerPoint animations, artworks, games here)
http://pptheaven.mvps.org
PowerPoint Heaven - The Power to Animate


"Mike H" wrote:

> Try
>
> =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O","")))
>
> Mike
>
> "tohlz" wrote:
>
> > Hi,
> > Lets say I want to look for the letter "O" in a cell with the text "How are
> > you?" and count the number of "O" in that particular cell, the formula would
> > be something like:
> > =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O",""))
> >
> > and the result for "How are you?" will return 2.
> >
> > My question is, what if I want to do it for multiple cells? For instance,
> > from Cell A1 to A10. Thanks in advance.
> > --
> > Shawn Toh (tohlz)
> > Microsoft MVP PowerPoint
> >
> > (Amazing PowerPoint animations, artworks, games here)
> > http://pptheaven.mvps.org
> > PowerPoint Heaven - The Power to Animate

Glad I could help and thanks for the feedback.

"tohlz" wrote:

> Thanks for the help, Andy and Mike.
> --
> Shawn Toh (tohlz)
> Microsoft MVP PowerPoint
>
> (Amazing PowerPoint animations, artworks, games here)
> http://pptheaven.mvps.org
> PowerPoint Heaven - The Power to Animate
>
>
> "Mike H" wrote:
>
> > Try
> >
> > =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O","")))
> >
> > Mike
> >
> > "tohlz" wrote:
> >
> > > Hi,
> > > Lets say I want to look for the letter "O" in a cell with the text "How are
> > > you?" and count the number of "O" in that particular cell, the formula would
> > > be something like:
> > > =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O",""))
> > >
> > > and the result for "How are you?" will return 2.
> > >
> > > My question is, what if I want to do it for multiple cells? For instance,
> > > from Cell A1 to A10. Thanks in advance.
> > > --
> > > Shawn Toh (tohlz)
> > > Microsoft MVP PowerPoint
> > >
> > > (Amazing PowerPoint animations, artworks, games here)
> > > http://pptheaven.mvps.org
> > > PowerPoint Heaven - The Power to Animate