使用带有变量范围的空白值标准的COUNTIF [英] using COUNTIF with blank value criteria with variable ranges
问题描述
我有这样一个奇怪的问题,试图使用 COUNTIF
。
I have sort of a bizarre issue here attempting to use COUNTIF
.
假装一会儿有问题的表:
pretend for a moment this is the sheet in question:
A B
John Doe
John Smith
John
最后一个值(B3)是一个空字段。
The last value there (B3) is an empty field.
如果John存在,则 COUNTIF
公式应计算 B
列中的空值数A列。
The intended COUNTIF
formula should count the number of empty values in the B
column only IF John is present in the A column.
我能够成功完成的唯一方法是明确指定要计数的范围( B1:B3
),但是这个公式将会在不是全部行数相同的多个工作表中进行,所以我不能使用 COUNTBLANK
因为如果我简单地将 B
列命名为一个名称,并指定名称作为范围,则返回的结果非常惊人。
The only way I have been able to do this successfully is explicitly specifying the range to be counted (B1:B3
), but this formula is going to be doing this on multiple sheets that do not all have the same number of rows, therefore, I cannot use COUNTBLANK
because it is returning staggeringly high results if I simply name the B
column a name and specify the name as the range.
编辑:
显然,不会用于那个?我找到的解决方法是使用 SUMPRODUCT
。这是最好的办法吗?
So apparently countif cannot be used for that? A workaround I have found is using SUMPRODUCT
. Is this the best way to go about doing this?
= SUMPRODUCT((September!K1:K16000 =John)*(九月! L1:L16000 =))
推荐答案
您可以使用 COUNTIFS
为多个条件。例如,您可以使用:
You can use COUNTIFS
for multiple criteria. For instance, you can use:
=COUNTIFS(A:A,"John",B:B,"")
这篇关于使用带有变量范围的空白值标准的COUNTIF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!