CountIf problem

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Is there a formula to count every occurence of a
character. The following formula counts the number of
cells with the letter 's'. =COUNTIF(A1:F35,"*s*"). I need
to have a count of all occurences of the letter 's'. So
if there is 2 's' in one cell, the count will show this.
Thanks for any help.
 
Hi,
Thanks to J. Walkenbach, if you name the range
A1:F35 "data", and put the letter s in a cell, and name
that cell "text", then the following formula will work,
note that it is not case sensitive:
=(SUM(LEN(data))-SUM(LEN(SUBSTITUTE(UPPER(data),UPPER
(text),""))))/LEN(text)

if you want one that is case sensitive, use:
=(SUM(LEN(data))-SUM(LEN(SUBSTITUTE(data,text,""))))/LEN
(text)

HTH
 
Thanks for your help. First time using this.
-----Original Message-----
Hi
see your other post
P.S.: please don't multipost

--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
Back
Top