Saturday, March 9, 2013

yogi_Tally Number Of Text Mentions In Cells With More Than One Entry Per Cell


                                          Google Spreadsheet   Post  #1074
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 9, 2013
user lostinflorida :(http://productforums.google.com/forum/?zx=nbznyz1cryoz#!category-topic/docs/spreadsheets/fok8yE5yyHs)
I have a summary sheet "Status Report" where I would like to count the number of mentions of Reading, Writing, Math from a single column (H) on the referring sheet (Students) . Column H may have a single entry (Math) or more than one entry separated by comma (Math, Reading)

I tried using =SUM( COUNTIF( 'Students'!H:H ; {"Math"}))

but it only returns the number of times "Math" appears alone.

Can someone provide the formulas I would use to count all mentions of Reading, all mentions of Math, and all mentions of Writing - regardless of what other text is in the cell.

many thanks!
-------------------------------------------------------------------------------------------------------------

following is a solution to a bit more generalized problem