I am trying to iterate a COUNTIF formula that counts the number of cells in each row containing numbers greater than 4. I am trying to use ArrayFormula to do this but it isn't working.
Unfortunately it counts ALL the cells matching the condition across the entire range B2:G39 resulting in 26 being entered in each row of column K rather than row by row count i.e. number of cells >4 in B2:G2 entered in K2, number of cells >4 in B3:G3 entered in K3
This is the formula
=ARRAY_CONSTRAIN(arrayformula( if( row(H:H)=1, "Number of cells >4",COUNTIF(A2:G39,">4"))),39,1)
Here is a spreadsheet that includes sample data and my formula
Try entering this in cell K2:
=ARRAYFORMULA(IF(ISBLANK(A2:A),IFERROR(1/0),MMULT(SIGN(A2:G>4),SIGN(TRANSPOSE(A2:G2)))))
By putting your data into matrix form, you can use the MMULT and SIGN functions with ARRAYFORMULA to achieve the conditional count over rows you're looking for.