CODEWITHSUNDEEP
google-sheetsarray-formulascountif
weizer
weizer

Reputation: 1117

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

enter image description here

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.

Upvotes: 1

Views: 568

Answers (1)

Wellerman
Wellerman

Reputation: 856

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

Upvotes: 3

Related Questions