Help with student ratings

[taniab]

I have cells that have data like

Cell 1 Con Con Con Con Con
Cell 2 Oft Con Som Con Som
They are codes for consistently, sometimes and often

I need a formula that will count the number of "Con"s in both cells,then the number of "Oft"'s etc. but they are separated by a space in cell 1 and cell 2.

 

[taniab]

So I have the formula, now I need to know how to count it over two cells, not just one. Ie. AT the moment it is only counting based on the contents of O12, I need it to count for O12, P12 and Q12.

=(LEN(O12)-LEN(SUBSTITUTE(O12,"Con","")))/LEN("Con")+(LEN(O12)-LEN(SUBSTITUTE(O12,"Oft","")))/LEN("Oft")

 

[taniab]

I got it working, but it's very messy, can someone simplify this for me?

=(LEN(P49)-LEN(SUBSTITUTE(P49,"Con","")))/LEN("Con")+(LEN(P49)-LEN(SUBSTITUTE(P49,"Oft","")))/LEN("Oft")+(LEN(Q49)-LEN(SUBSTITUTE(Q49,"Con","")))/LEN("Con")+(LEN(Q49)-LEN(SUBSTITUTE(Q49,"Oft","")))/LEN("OFt")+(LEN(R49)-LEN(SUBSTITUTE(R49,"Con","")))/LEN("Con")+(LEN(R49)-LEN(SUBSTITUTE(R49,"Oft","")))/LEN("Oft")+(LEN(S49)-LEN(SUBSTITUTE(S49,"Con","")))/LEN("Con")+(LEN(S49)-LEN(SUBSTITUTE(S49,"Oft","")))/LEN("Oft")+(LEN(T49)-LEN(SUBSTITUTE(T49,"Con","")))/LEN("Con")+(LEN(T49)-LEN(SUBSTITUTE(T49,"Oft","")))/LEN("Oft")+(LEN(U49)-LEN(SUBSTITUTE(U49,"Con","")))/LEN("Con")+(LEN(U49)-LEN(SUBSTITUTE(U49,"Oft","")))/LEN("Oft")+(LEN(V49)-LEN(SUBSTITUTE(V49,"Con","")))/LEN("Con")+(LEN(V49)-LEN(SUBSTITUTE(V49,"Oft","")))/LEN("Oft")+(LEN(W49)-LEN(SUBSTITUTE(W49,"Con","")))/LEN("Con")+(LEN(W49)-LEN(SUBSTITUTE(W49,"Oft","")))/LEN("Oft")