yogi_Formula For COUNTIF On Multiple Substrings

                    Google Spreadsheet   Post  #1775
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-27-2014
post by  Toby Carson:
(https://productforums.google.com/forum/#!mydiscussions/docs/7bzxWf30qkc)
COUNTIF on multiple substrings

Sorry if the answer is already out there - I'm going Goggle-Eyed searching.

I have a large sheet (Form Responses) populated by staff at my school selecting items (mostly subjects) off a list of checkboxes in a form,

I have another sheet that simply lists those checkbox subjects in column A,

I'm keen to count how many times each subject has been checked in column B,

=COUNTIF('Form Responses'!D:D,"*"&A2&"*") does a pretty good job at this, however...

The complication is that some categories recur so that staff can award 2 points rather than the standard single (eg maths is 1 mark, maths*2 is 2 marks)

on some occasions, they may award 3 marks by combining both of these.

The formula above will only record one instance of the substring but apparently not the second one should it appear twice in the cell.

Any clever people got a simple solution?

Very grateful,

Toby

-------------------------------------------------------------------------------------------------------------------------------