Countif code that counts cells based on text string specified in a reference cell

Excel fans,
I have a countif application problem that's illustrated in the attached file. It's intended to tally up the frequency of individual defect codes that appear (with other defect codes) in a column of raw data entry cells. I'm trying to write a countif formula that will count the cells in the raw data column that contain a specific data string that's contained in a reference cell to the left of the formulae. The problem is the targeted defect code (text string) could exist anywhere in the raw data cell. For example, the targeted string might be 'CL'. That string could be the only one in a given raw data cell or it could be embedded anywhere in a raw data cell amongst other defect strings. For example, a raw data cell might read, 'CL' or it might read 'CL,HAD,COH' which means the sample contained 3 different defects (from a vast array of possible defect codes) which were found and recorded in the order shown. However, the cell might instead read, 'HAD,CL,COH' or 'HAD,COH,CL'.
A second problem is that there are similar codes, such as 'CLX' which contain the targetted code, such as 'CL'.
I haven't been able to write the countif formula that counts the target stringe and only the target string regardless of where it appears in a raw data cell and doesn't mistake, for example the CL in CLX as target.

The attached file illustrates the table, several flawed formulae and where they go wrong.
drop test visual code tally snip-it.xlsx

I would very much appreciate any help someone might be able to provide.
Thank you,
John Quinn

JieJenn,
Thank you very much for your quick and valuable recommendation.
Gratefully,
John Quinn