Monday, August 4, 2014

yogi_From Column A Count Instances of Items Listed In Column D

                          Google Spreadsheet   Post  #1720
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-04-2014
post by gahoo:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/1wVKFiq1jOI)
Filtering and counting formula problem with "overlapping" words
I am looking to count the number of times certain words are contained in a column. The column is populated from a google form using the checkbox, so each submission is a row and the result is an comma delineated array in the column I need to count. Easy example:

Row 1: Apple, Banana, Carrot
Row 2: Apple, Carrot
Row 3: Carrot
Row 4: Apple, Carrot
Row 5: Banana, Carrot

I can count these OK using the following, replacing "Apple" as needed:

=COUNTA( IFERROR( FILTER( A:A , SEARCH( "Apple"&"," , A:A&"," ) ) ) )

My problem comes in when I have "Green Apple" as another fruit.

Row 1: Apple, Banana, Carrot, Green Apple
Row 2: Apple, Carrot
Row 3: Carrot, Green Apple
Row 4: Apple, Carrot
Row 5: Banana, Carrot

Now, the above formula counts "Green Apple" correctly, but when set to count "Apple", counts the Green Apple entries as well, and then only counts 1/row. So counting for "Apple" here returns a value of 4.

Here is a quick test spreadsheet showing the issue. 

https://docs.google.com/spreadsheets/d/1bE802GwelRK-qcs-suR-Wusa716h9bhmlVFGhyj7S4g/edit?usp=sharing


Is there a better way to count here?  Thanks in advance for any help.
------------------------------------------------------------------------------------------------------------------------------------------