CODEWITHSUNDEEP
google-sheetsspreadsheet
Timmy D
Timmy D

Reputation: 1

Using ArrayFormula with CountIF and IsBlank

I have a Google Form that asks a series of Yes/No questions. I'm trying to sum the number of Yes responses in each row as a new submission is entered.

enter image description here

Here's what I have, that doesn't work.

=ARRAYFORMULA(if(isblank(E$2:E),"",(COUNTIF(A2:D2,"yes"))))

It works for one cell if I remove isBlank, and just do this: 

=ARRAYFORMULA(COUNTIF(A2:D2,"yes"))

But then I've lost the benefit of using "isBlank" to autopopulate for new form submissions. I'm really trying to avoid doing this with hidden sheets.

Upvotes: 0

Views: 1168

Answers (2)

TheMaster
TheMaster

Reputation: 50452

=ARRAYFORMULA((IF(A2:A<>"",MMULT(--(A2:D="yes"),ROW(A1:A4)^0),"")))

Upvotes: 0

JPV
JPV

Reputation: 27262

In E1 try this formula

={"Count";arrayformula(if(len(A2:A), mmult(--(B2:D="Yes"), transpose(B1:D1^0)),))}

Change range to suit.

Upvotes: 1

Related Questions