Count if contains yes using array formula for Google Form entries

Question

I want to count every yes per submission of each Google Form, so the row.
Some of the responses have comments after the yes and it won't count those. This is the formula I am using:

={"Score"; ArrayFormula(if(len(A2:A), countif(if(C2:AP="yes", row(C2:C)), row(C2:C)),))}

I tried changing the yes to include asterisks around the yes, but that didn't work either. That made it not count any yes.

Answer 1

Try the following regex formula

={"Score"; 
   INDEX(if(len(B2:B), 
             countif(if(REGEXEXTRACT(D2:G,"(?i)yes")="yes", row(D2:D)), row(D2:D)),))}

(do adjust ranges to your needs)

(?i) makes the regex case insensitive.
To turn the formula to case sensitive just omit it.

New functions used:

• INDEX
• REGEXEXTRACT