Counting specific letters in multiple cells

[tohlz]

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

 

[Andy Pope]

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

 

[Mike H]

Try

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

Mike

 

[tohlz]

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]

Glad I could help and thanks for the feedback.

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