count # of instances in cell (a b a) answer 2 formula if possible

[Guest]

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.

 

[Guest]

Hi Eric,

you could do using this module, press ALT+F11 copy it and paste.

___________________________________________
Function CountChar(MyChar, Mystring)
Dim counter As Integer

CountChar = 0

For counter = 1 To Len(Mystring)
If Mid(Mystring, counter, 1) = MyChar Then CountChar = CountChar + 1
Next counter

End Function
__________________________________

So the formula will be =countchar("a",E7)

does this help?
regards from Brazil
Marcelo



"eric" escreveu:

 

[Dave Peterson]

=(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")

 

[Guest]

Thanks that worked.

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

 

[Bob Phillips]

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a")

--

HTH

Bob Phillips

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

 

[Bob Phillips]

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)

 

[Guest]

Great, that worked perfect. Thanks to everyone

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)