Formula request

J

Joe

I need to find a formula that will tell me the frequency of a character appearing within a range of text.
For instance, A1 has JUICY and A2 has JUMBO. If I want to find the number of times J appears, it's 2 times

Thanks you.
 
D

Dave Peterson

One way:
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"J","")))
(watch out =substitute is case sensitive, so it won't count lower case j's.


If you want to count both j's and J's:
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"J","")))
 
Top