How to apply ARRAYFORMULA together with COUNTIF in google sheet?

user_15002748 0
Viewed 131

enter image description here

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!

countif array-formulas google-sheets-formula google-sheets
1 Answers

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")))

enter image description here

user_56326290
Related