Excel公式中如何正确引用内部创建的范围？解决COUNTIF溢出问题

你的问题核心是COUNTIF的第一个参数返回了溢出数组，在动态数组版本的Excel中，这种数组会自动溢出到下方单元格，且原公式逻辑存在错误——IF(A:A="Approved",B1,"y")里的B1是固定单元格，并非对应A列每行的Key值，导致统计结果不准确。

以下是两种可实现自包含公式且避免溢出的方法：

方法1：使用COUNTIFS函数（简洁高效） #

COUNTIFS支持多条件直接统计，无需嵌套IF，且不会产生溢出：

表格内公式： #

=IF([@[Event Name]]="Reviewed",COUNTIFS([Event Name],"Approved",[Key],[@Key]),"")

普通单元格等效公式： #

=IF(A1="Reviewed",COUNTIFS(A:A,"Approved",B:B,B1),"")

逻辑说明：仅当当前行的「Event Name」为"Reviewed"时，统计所有「Event Name」为"Approved"且「Key」与当前行「Key」一致的记录数；否则返回空值。

方法2：用SUMPRODUCT替代COUNTIF（兼容旧版Excel） #

若需兼容不支持动态数组的旧版Excel，或明确处理数组且避免溢出，可使用SUMPRODUCT：

表格内公式： #

=IF([@[Event Name]]="Reviewed",SUMPRODUCT(--([Event Name]="Approved"),--([Key]=[@Key])),"")

普通单元格等效公式： #

=IF(A1="Reviewed",SUMPRODUCT(--(A:A="Approved"),--(B:B=B1)),"")

逻辑说明：通过--将布尔判断结果转换为1/0，SUMPRODUCT计算同时满足两个条件的行的乘积和，本质就是统计符合条件的记录数，且不会触发溢出。

原公式溢出原因 #

原公式中IF(A:A="Approved",B1,"y")会生成一个与A列行数相同的数组，在动态数组Excel中，COUNTIF接收数组参数时会自动溢出到下方单元格。即便给数组添加@强制返回单个值，原公式中固定引用B1的逻辑错误也会导致统计结果不正确，因此不建议采用这种修正方式。

内容的提问来源于stack exchange，提问作者michael