Great, that worked perfect. Thanks to everyone

"Bob Phillips" wrote:

Sorry, I meant

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER("a"),"")))/LEN("
a"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"eric" wrote in message
...
Thanks that worked.

Is there a way to get a total for a range of cells with the formula below?

"Dave Peterson" wrote:

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a")
Will count the number of A's or a's in A1.

If you want just the lower case a's:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a")

eric wrote:

I can't get the count of the number of intances of a value to work.

ex: In single cell (a b a) answer=2 for "a", but does not work with
any
formula I have tried.

=countif(data, "*"&text&"*") brings an answer of 0.

I was wondering if anyone knows how or if it is possbile to do this.

--

Dave Peterson