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

E

eric

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.
 
M

Marcelo

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:
 
D

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")
 
E

eric

Thanks that worked.

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

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)
 
B

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)
 
E

eric

Great, that worked perfect. Thanks to everyone

Bob Phillips said:
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)
Click to expand...
 
Top