0

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.

Improve this question

1 Answer 1

Reset to default
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)

enter image description here

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

New functions used:

Improve this answer
3
  • Thank you very much! It worked perfectly!!
    – Kim
    Commented Mar 15, 2021 at 18:57
  • "Thank you very much! It worked perfectly!!" Please remember that you can also contribute to this community that helps you. As per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well.
    – marikamitsos
    Commented Mar 16, 2021 at 0:44
  • Can I make this so that it doesn't count the empty cells?
    – Kim
    Commented Mar 26, 2021 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.