iterate row by row COUNTIF using ArrayFormula on Google Sheets

google-sheets spreadsheet countif array-formulas
herteladrian picture herteladrian · Jun 22, 2017 · Viewed 9.1k times · Source

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

Answer

Greg picture Greg · Jun 23, 2017

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.

Related questions

Google Spreadheets Scripts: check if cell is empty

I want to input a variable in a cell only if the cell is empty. The if statement, however, does not work. Any advice? var ss=SpreadsheetApp.getActiveSpreadsheet(); var r=ss.getRange("'odpovědi'!A2:J"); var rws=r.getNumRows(); …

Read More
Spreadsheets get unique names but ignore blank cells

I would like to use spreadsheets to get all unique names from Column A in a table but in the same time I would like blank cells to be ignored. So far I've got this formula that returns all of …

Read More
Create in-cell dropdown with filtered range

I'm using Google Spreadsheet. To illustrate my problem, I use the range A2:A8 for the data validation of D2 and E2. But because in cell D2, you are supposed to select an animal only, I'd like to filter the …

Read More