Hi everyone,
I want to count how many Students choose "Yes" and how many Students choose "No". Each student will be given 5 rows, so the formula need to check whether any "Yes" or "No" exist in these 5 rows. Only count once even though there might be more than one "Yes" or "No" for one student.
Initially I'm using COUNTIF(B2:B6,"Yes")
in column C and then use COUNTIF(C2:C6,"<>0")
in column D. Finally, I use COUNTIF(D2:D31,"<>0")
to obtain Number of student choose "Yes" or "No". I think it is to tedious to do it manually, is there any way can make this process simpler, maybe using Arrayformula
or Query
(Not sure how it can work together with Countif
)? Any help will be greatly appreciated!
try:
=ARRAYFORMULA(TRANSPOSE(QUERY(UNIQUE({IF(B2:B="",,
VLOOKUP(ROW(A2:A), IF(A2:A<>"", {ROW(A2:A), A2:A}), 2, 1)), B2:B}),
"select count(Col1) where Col2 is not null pivot Col2")))