iterate row by row COUNTIF using ArrayFormula on Google Sheets

Question

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

Solution 1

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.

Solution 2

Sample File

I found that mmult is very slow and may cause your file loading a long time. I suggest using len + join (with a query) instead (speed is not tested):

={"RegexReplace-Len..."; ArrayFormula(LEN(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(FILTER(--(A2:P>5),A2:A<>"")),,100500)),"[ 0]", "")))}

Also here's a solution by JPV:

=filter(COUNTIF(IF(A2:P>5, ROW(A2:A)), ROW(A2:A)), A2:A<>"")

Change A2:P to your range, A2:A to a key column with no blank cells.

Comments

[herteladrian]

Thank you @Greg. that worked! Why does my formula not work? Curious to understand arrayformulas limitations

[Greg]

My pleasure, @herteladrian. Your formula, reduced to =arrayformula(COUNTIF(A2:G39,">4")) for ease of discussion, doesn't work because the range argument A2:G39 is "commandeered" by the COUNTIF function and used in its computation, returning a result that is not an array. In other words, ARRAYFORMULA never sees the input range, so it has no chance to substitute for it. This is why ARRAYFORMULA does not work with AND and OR functions also.