EXCEL:计数范围；但排除空白和特定字符串

Question

Excel版本：Microsoft365包(V2106Build: 14131.20278)

我正在尝试创建一个Excel公式，该公式将数字输出到单个单元格(B11)；该公式必须：

在给定范围内，B2:B8;

• Therefore，IT必须计数具有数据的单元格数，而不是计数非空白单元格；但也必须不包括具有特定字符串的E 215单元格(在这种情况下，字符串为“E 116N/AE 217”)；H 218公式还必须计数E 120行中断E 221所以在给定范围内出现的字符(10)。H 222f 223/>

--我知道，在一个范围内计算输入的行换行并不是传统的做法，但这正是我所需要的。

见附图：

​

​

range =在本例中，我要计数的(非空白和非“N/A”)单元格的范围从B2到B8。

单元w/公式：B11

到目前为止，我只做了以下公式。

=SUM(LEN(B2:B8)-LEN(B2:B8;CHAR(10);""))+(LEN(B2:B8)>0))

这个公式输出 == "9“(即9个换行:忽略空格，但包含字符串"N/A")

这个公式允许我计算在给定范围内出现的所有换行数(因此，它不计算空白/空单元格)，但显然不排除特定字符串"N/A“的。

期望输出 == "8“(即8个换行:忽略空白单元格和字符串”N/A“的单元格)

我想这可以用一个“如果”来完成，但我还没有想清楚。

谢谢您抽时间见我!

PS:我试图在这里做一个表，但我无法在单个单元格中添加换行；因此，我最后求助于一个图像来解释我的观点，很抱歉给您带来不便！)

Answer 1

对，既然您可以访问Microsoft 365，请尝试以下操作：

=SUM(FILTER(1+(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,CHAR(10),""))),LEN(B2:B8)*(B2:B8<>"N/A"),0))

很好地安排了问题，还有一个有趣的小问题要处理。希望以上这些都能帮到你。

Answer 2

只是为了一种不同的方法：

( @ScottCraner之后的编辑指出它没有说明N/A)

=LET(x,FILTER($B$2:$B$8,B2:B8<>"N/A"),
     y,TEXTJOIN(CHAR(10),TRUE,x),
     z,SUBSTITUTE(y,CHAR(10),""),
   LEN(y)-LEN(z)+1)

当然，如果您有大量数据，如果结果字符串> 32,767个字符，TEXTJOIN将失败。

Answer 3

应该能够用一个简单的COUNTIF (或者COUNTIFS，如果您想要添加更多的条件来计数)来完成这个任务。

试试=COUNTIF($B$2:$B$8, "<>N/A")

EDIT1:正如cybernetic.nomad所指出的，处理非数字数据的更好方法将是COUNTA。另一种解决方案可能是使用以下内容过滤整个数据：

=COUNTA(FILTER($B$2:$B$8, $B$2:$B$8<>"N/A"))

EDIT2:正如Scott所指出的，这种解决方案不考虑换行。您可以通过以下方式添加这些内容：

=COUNTA(FILTER($B$2:$B$8, $B$2:$B$8<>"N/A"))+
SUM(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8,CHAR(10),"")))