Menu
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
439
asked Jun 22 '17 18:06
ArrayFormula with SUMIF and COUNTIFYou can deploy Array Formulas with aggregation functions, such as SUMIF and COUNTIF, to quickly add values within a range.
To use it in Google Sheets, you can either directly type “ARRAYFORMULA” or hit a Ctrl+Shift+Enter shortcut (Cmd + Shift + Enter on a Mac), while your cursor is in the formula bar to make a formula an array formula (Google Sheets will automatically add ARRAYFORMULA to the start of the 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.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
