Counting in cells

I would like to be able to count the alpha characters in a range of cells.
For ex:
a1 = M C D M M
a2 = M C C
then C=3, D=1 and M =4.

Bud Phillips has LEN(A1)-LEN(SUBSTITUTE(A1,"G","")) which works great for
one cell, but I can't get it to work for a range.

If I use +COUNTIF($A$1:$A$2,"*M*") it does not count multiples in a cell it
would =2.

Any help is truly appreciated.

Thanks

One way

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ronbo" <[email protected]> wrote in message
news:[email protected]...
>I would like to be able to count the alpha characters in a range of cells.
> For ex:
> a1 = M C D M M
> a2 = M C C
> then C=3, D=1 and M =4.
>
> Bud Phillips has LEN(A1)-LEN(SUBSTITUTE(A1,"G","")) which works great for
> one cell, but I can't get it to work for a range.
>
> If I use +COUNTIF($A$1:$A$2,"*M*") it does not count multiples in a cell
> it
> would =2.
>
> Any help is truly appreciated.
>
> Thanks
>
>
>
>
>

"Ron de Bruin" wrote:

> One way
>
> =SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"G",""))))
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Ronbo" <[email protected]> wrote in message
> news:[email protected]...
> >I would like to be able to count the alpha characters in a range of cells.
> > For ex:
> > a1 = M C D M M
> > a2 = M C C
> > then C=3, D=1 and M =4.
> >
> > Bud Phillips has LEN(A1)-LEN(SUBSTITUTE(A1,"G","")) which works great for
> > one cell, but I can't get it to work for a range.
> >
> > If I use +COUNTIF($A$1:$A$2,"*M*") it does not count multiples in a cell
> > it
> > would =2.
> >
> > Any help is truly appreciated.
> >
> > Thanks
> >
> >
> >
> >
> > Thanks, it works perfect.
>
>
>