CODEWITHSUNDEEP
google-sheets
friedman
friedman

Reputation: 685

ARRAYFORMULA COUNTIF each row separately

I try to COUNTIF(B2:Q2;">5") for each row and return the result for each row in column A but I clearly fail on using ARRAYFORMULA for this purpose.

Could you help?

Upvotes: 3

Views: 3997

Answers (2)

JPV
JPV

Reputation: 27302

In addition to Max's solution, I believe it should be possible to use COUNTIF(). See if this works

=ARRAYFORMULA(COUNTIF(IF(B2:Q>5,ROW(B2:B)), ROW(B2:B)))

Upvotes: 9

Max Makhrov
Max Makhrov

Reputation: 18727

Sample File

=ARRAYFORMULA(MMULT(FILTER(--(B2:Q>5),B2:B<>""),TRANSPOSE(COLUMN(B2:Q)^0)))

mmult is effective, but slow formula. I used filter to limit the number of calculations.

Edit. Here's another formula to do the same:

=ArrayFormula(LEN(SUBSTITUTE(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(FILTER(--(B2:Q>5),B2:B<>"")),,100500)),"0", "")," ","")))

Looks clunky but should work faster (not tested).

Upvotes: 2

Related Questions