关闭工作簿时Excel跨表CountIf/SumIf替代公式问询

由于引用已关闭的SourceData.xlsx时，COUNTIF/SUMIF无法正常工作，且直接使用整列范围会导致SUMPRODUCT/COUNT(IF)出现#SPILL!错误，我们通过非空LibraryID列构建动态数据范围，仅包含有效数据行，同时使用AGGREGATE/SUMPRODUCT函数处理错误值和条件统计。

假设SourceData.xlsx的数据位于Sheet1，列对应关系：

• LibraryID：A列（表头A1）
• FeesDue：B列（表头B1）
• Returned：C列（表头C1）

1. 忽略文本/错误的FeesDue求和公式 #

使用AGGREGATE函数（支持忽略错误值，兼容关闭文件引用）：

=AGGREGATE(9,6,SourceData.xlsx!$B$2:INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A)))

• 参数9表示求和，6表示忽略错误值；
• INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A)) 定位到B列最后一行有效数据（与非空LibraryID行同步）。

2.A. FeesDue列中数值大于0的记录数公式 #

=AGGREGATE(2,6,--(SourceData.xlsx!$B$2:INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A))>0))

• 参数2表示统计数字个数；
• --(...)>0 将条件判断结果转为1/0，AGGREGATE忽略错误值后统计符合条件的数量。

或用SUMPRODUCT实现：

=SUMPRODUCT(--(ISNUMBER(SourceData.xlsx!$B$2:INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A))),--(SourceData.xlsx!$B$2:INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A))>0)))

• ISNUMBER确保只统计数值型数据，排除文本/错误值。

2.B. FeesDue列中数值等于0的记录数公式（动态适配行数） #

=SUMPRODUCT(--(ISNUMBER(SourceData.xlsx!$B$2:INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A))),--(SourceData.xlsx!$B$2:INDEX(SourceData.xlsx!$B:$B,COUNTA(SourceData.xlsx!$A:$A))=0)))

• 基于LibraryID的非空行构建动态范围，自动适应数据行数变化；
• ISNUMBER排除错误值（如示例中的#N/A），仅统计数值为0的记录。

3. Returned列中“Y”的计数公式 #

=SUMPRODUCT(--(SourceData.xlsx!$C$2:INDEX(SourceData.xlsx!$C:$C,COUNTA(SourceData.xlsx!$A:$A))="Y"))

• 动态范围仅包含有LibraryID的有效行，避免空行干扰；
• --(...)="Y" 将匹配结果转为1/0，SUMPRODUCT求和得到计数。

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