使用具有可变范围的空白值标准的 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.

预期的 COUNTIF 公式应该计算 B 列中空值的数量，前提是 A 列中存在 John.

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.

所以显然cou​​ntif不能用于那个?我发现的一种解决方法是使用 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")*(September!L1:L16000=""))

推荐答案

您可以将 COUNTIFS 用于多个条件.例如，您可以使用:

You can use COUNTIFS for multiple criteria. For instance, you can use:

=COUNTIFS(A:A,"John",B:B,"")

这篇关于使用具有可变范围的空白值标准的 COUNTIF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！