Sunday, August 3, 2014

yogi_Count Instances Of a Specified String In A Range Of Cells Depending On Case Sensitivity as Specified

                          Google Spreadsheet   Post  #1720
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Aug-01-2014
post by Alby Johnson:
Counting the number of TIMES (not cells) a string occurs within a column
I want to be able to add up the total number of times a specific string occurs within a column.

Specifically (for the time being) I would like to count the number of times the string "rnav" occurs in the column H5:H. There are several cells which contain the string "rnav" more than once - the formula must count each occurrence.
So for example: If there is only two cells that contain "rnav": H100 = "rnav" and H200 = "rnav blabla rnav bla rnav", the result must be 4 seeing as though the string "rnav" has occurred a total of 4 times, even though it is only in a total of 2 cells.

What I actually want to do is count how many times "rnav", "vor" and "ndb" occur in column H5:H where date(A5:A,B5:B,C5:C)>=edate(today(),-3). In other words: how many times do those strings occur in column H within the last 3 months. Once I can figure out how to count the number of times a string occurs (and not just the number of cells that string is found in), the rest will be easy.

Many Thanks
hanks Yogi and Isai for the extra information.

I actually discovered a minor issue with Bobs formula in that it was also counting commas but was easy fixed by using Isai's char(9) in the regexreplace function instead of ","

Yogis formula works perfectly fine and is surprisingly simple by just using LEN. Is there a way to make it non case sensitive? Its fine for me as it is because the strings im searching for will always be entered the same but it would be good to know if the substitute function can replace a string regardless of its case. I would be using this formula but I cant figure out how to filter it in the same way as I have done in my first reply a few posts up (only counting the characters in column H where the date (in columns A,B,C) is no more than 3 months ago).

This is all good information and it has opened up a number of extra features I can add which will eliminate the need to manually sift through a constantly increasing dataset for specific occurrences based on all sorts of criteria.
Thanks everyone for your help