ARRAYFORMULA COUNTIF每行分别 [英] ARRAYFORMULA COUNTIF each row separately

问题描述

我尝试为每一行COUNTIF(B2:Q2;">5")并返回列A中每一行的结果，但是我显然无法为此目的使用ARRAYFORMULA.

I try to COUNTIF(B2:Q2;">5") for each row and return the result for each row in column A but I clearly fail on using ARRAYFORMULA for this purpose.

你能帮忙吗?

推荐答案

样本文件

=ARRAYFORMULA(MMULT(FILTER(--(B2:Q>5),B2:B<>""),TRANSPOSE(COLUMN(B2:Q)^0)))

mmult是有效的，但是公式较慢.我用filter限制了计算次数.

mmult is effective, but slow formula. I used filter to limit the number of calculations.

编辑.这是另一个执行相同操作的公式:

Edit. Here's another formula to do the same:

=ArrayFormula(LEN(SUBSTITUTE(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(FILTER(--(B2:Q>5),B2:B<>"")),,100500)),"0", "")," ","")))

看上去笨拙，但应该运行得更快(未经测试).

Looks clunky but should work faster (not tested).

这篇关于ARRAYFORMULA COUNTIF每行分别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！