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

Similar Q is here: https://stackoverflow.com/questions/49147713/arrayformula-countif-each-row-separately — Max Makhrov, Aug 29 '18 at 13:45
Does this answer your question? [ARRAYFORMULA COUNTIF each row separately](https://stackoverflow.com/questions/49147713/arrayformula-countif-each-row-separately) — marikamitsos, Sep 30 '20 at 15:04

score 5 · Accepted Answer · answered Jun 23 '17 at 02:29

5

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.

answered Jun 23 '17 at 02:29

Greg

1,264
14
21

Thank you @Greg. that worked! Why does my formula not work? Curious to understand arrayformulas limitations – herteladrian Jun 23 '17 at 18:05
4

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. – Greg Jun 27 '17 at 16:34

score 4 · Answer 2 · edited Sep 08 '18 at 19:11

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.

iterate row by row COUNTIF using ArrayFormula on Google Sheets

2 Answers2

Linked