How to use arrayformula in googlesheet to count the column with condition?

Question

Hi everyone,

I'm trying to count the number of "Yes" appeared in each row. I want to use array formula so that I'm not required to have formula in each cell in column G. May I know arrayformula can achieve this or there is other method? I tried to use arrayformula(countif(B4:F4,"Yes")) but nothing come out for row 5,6,7,8. Hope to get some advice on this problem as I'm new to google sheet. Thank you.

Answer 1

Give this a try.

=ARRAYFORMULA(COUNTIF(IF(B4:F="Yes", ROW(B4:B8)), ROW(B4:B8)))

Edit: For the case that you describe below (only including certain columns), it's a little more involved, but the same principle works.

=ARRAYFORMULA(COUNTIF(IF(B4:B="Yes", ROW(B4:B8)), ROW(B4:B8)) + COUNTIF(IF(D4:D="Yes", ROW(D4:D8)), ROW(D4:D8)) + COUNTIF(IF(F4:F="Yes", ROW(F4:F8)), ROW(F4:F8)))