在Google表格上使用ArrayFormula逐行迭代COUNTIF [英] iterate row by row COUNTIF using ArrayFormula on Google Sheets

问题描述

我正在尝试迭代一个COUNTIF公式，该公式对每行包含大于4的数字的单元格进行计数.我正在尝试使用ArrayFormula来执行此操作，但是它不起作用.

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.

不幸的是，它在整个范围B2:G39中对所有符合条件的单元格进行计数，导致在K列的每一行中输入了26个，而不是逐行计数，即在K2中输入的B2:G2中的单元格数大于4，在K3中输入的B3:G3中> 4的单元数

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

这是公式

=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

推荐答案

尝试在单元格K2中输入此内容:

Try entering this in cell K2:

=ARRAYFORMULA(IF(ISBLANK(A2:A),IFERROR(1/0),MMULT(SIGN(A2:G>4),SIGN(TRANSPOSE(A2:G2)))))

通过将数据放入矩阵形式，您可以使用 MMULT 和 SIGN 函数与ARRAYFORMULA一起实现对行的条件计数您正在寻找.

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.

这篇关于在Google表格上使用ArrayFormula逐行迭代COUNTIF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！