3

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?

friedman
  • 645
  • 10
  • 25
  • Similar Q is here: https://stackoverflow.com/questions/44707078/iterate-row-by-row-countif-using-arrayformula-on-google-sheets – Max Makhrov Aug 29 '18 at 13:45
  • @MaxMakhrov how could I get your solution to work with my countif formula below, where I'm counting the instances of TRUE across a range of cells in a row? I tried plugging it into yours but couldn't get it to work. =COUNTIF(U5:X5,"'TRUE")>=3 – user14915635 Feb 07 '22 at 20:31

2 Answers2

9

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)))
JPV
  • 26,499
  • 4
  • 33
  • 48
2

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

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81