Count If Contains Yes Using Array Formula – Google Sheets

formulasgoogle sheetsgoogle-formsgoogle-sheets-arrayformularegex

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.

Best Answer

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)

enter image description here

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

New functions used:

Related Topic