Hi,
I am trying to use an Array Formula on a Google Form Responses sheet - so the formula needs to be able to populate down for the whole column.
I am trying to count the number of "Yes" per row (max 4) and then if there are 4 return "Yes" (or "No" if there are not). The basic formula would be (and it works):
=if(countif(AA2:AD2, "Yes")=4, "Yes", "No")
However this will not work as an array formula for obvious reasons.
So far I have got the below to work:
=ARRAYFORMULA(if(row(A:A) = 1, "Test One", if(len(A1:A), COUNTIF(if(AA1:AD= "Yes", row(AA1:AA)), row(AA1:AA)),)))
However this returns a number and I still it to return the Yes/No if it is equal to 4 or not.
I have tried to put an if statement in various places to do this however I seem to break it every time.
Does any one please have any suggestions on how to make this work? I have attached a picture if this helps explain it!
Thanks!
CodePudding user response：
When working with array formulas and results row by row, you can consider using BYROW. Try with:
=BYROW(AA2:AD,LAMBDA(each.IF(countif(each, "Yes")=4, "Yes", "No")))
If you want not to have "No" when there are empty rows you can do:
=BYROW(AA2:AD,LAMBDA(each.IF(COUNTA(each)=0,"",IF(countif(each, "Yes")=4, "Yes", "No"))))
This checks if there is any value in those four columns
Or use:
=BYROW(AA2:INDEX(AD:AD,MAX(ROW(A:A),A:A<>"")),LAMBDA(each.IF(countif(each, "Yes")=4, "Yes", "No")))
This limits the BYROW until the last column with values. Change A:A if needed with any column you know that will always be completed by the Form
CodePudding user response：
try:
=INDEX(BYROW((AA2:AD="yes")*1, LAMBDA(x, SUM(x))))
to remove zeros:
=INDEX(BYROW((AA2:AD="yes")*1, LAMBDA(x, IFERROR(1/(1/SUM(x))))))
to return yes/no:
=INDEX(IF(Y2:Y="",,IF(Y2:Y=4, "yes", "no")))