针对已关闭链接工作簿的COUNTIFS与SUMIFS函数替代方案求助

我懂你这种头疼的感觉——COUNTIFS和SUMIFS确实没法直接对已关闭的工作簿进行计算，折腾起来特别闹心。下面给你两个实用的替代方案，分别对应你的两个公式需求，亲测好用：

你的原公式需求是：统计关闭工作簿Industrial - Feb.xlsx的Sheet1中，I列日期小于今日，且M列是To Do或Preparing的行数。

方法1：Power Query（推荐，稳定高效） #

这是最靠谱的长期解决方案，支持关闭工作簿计算，还能轻松刷新数据：

1. 点击Excel顶部「数据」选项卡 → 「获取数据」→ 「从文件」→ 「从Excel工作簿」，选中Industrial - Feb.xlsx
2. 在弹出的导航器里选择Sheet1，点击「加载到」→ 选择「仅创建连接」，记得勾选「将此数据添加到数据模型」
3. 回到你的工作表，输入下面的公式即可：

=COUNTROWS(FILTER('Industrial - Feb.xlsx'[Sheet1],'Industrial - Feb.xlsx'[Sheet1][I]<TODAY() && ('Industrial - Feb.xlsx'[Sheet1][M]="To Do" || 'Industrial - Feb.xlsx'[Sheet1][M]="Preparing")))

方法2：数组公式（适合临时快速计算） #

如果只是临时用一下，不想折腾Power Query，可以试试这个SUMPRODUCT结合数组的写法：

=SUMPRODUCT(--(INDEX('[Industrial - Feb.xlsx]Sheet1'!$I:$I,ROW($I:$I))<TODAY()),--(OR(INDEX('[Industrial - Feb.xlsx]Sheet1'!$M:$M,ROW($M:$M))="To Do",INDEX('[Industrial - Feb.xlsx]Sheet1'!$M:$M,ROW($M:$M))="Preparing")))

注意：旧版Excel输入完要按Ctrl+Shift+Enter确认，新版Excel会自动识别数组公式。但这个方法有个小缺点——如果文件路径或文件名改了，公式就会失效，不如Power Query稳定。

你的原公式需求是：对关闭工作簿6401C30006715.xlsx的SAPUI5 Export工作表，统计A列日期在C2单元格所在月份内，且D列等于B4单元格的G列数值之和。

方法1：Power Query（推荐） #

同样用Power Query来实现，稳定又省心：

1. 按照上面的步骤，导入6401C30006715.xlsx的SAPUI5 Export工作表，创建连接并添加到数据模型
2. 输入下面的公式：

=SUMX(FILTER('6401C30006715.xlsx'[SAPUI5 Export],'6401C30006715.xlsx'[SAPUI5 Export][A]>=C$2 && '6401C30006715.xlsx'[SAPUI5 Export][A]<=EOMONTH(C$2,0) && '6401C30006715.xlsx'[SAPUI5 Export][D]=$B4),'6401C30006715.xlsx'[SAPUI5 Export][G])

就算工作簿关闭，只要连接没动，就能正常计算，想更新数据点一下刷新就行。

方法2：SUMPRODUCT数组公式 #

临时使用的话，这个数组公式也能解决问题：

=SUMPRODUCT(--(INDEX('[6401C30006715.xlsx]SAPUI5 Export'!$A:$A,ROW($A:$A))>=C$2),--(INDEX('[6401C30006715.xlsx]SAPUI5 Export'!$A:$A,ROW($A:$A))<=EOMONTH(C$2,0)),--(INDEX('[6401C30006715.xlsx]SAPUI5 Export'!$D:$D,ROW($D:$D))=$B4),INDEX('[6401C30006715.xlsx]SAPUI5 Export'!$G:$G,ROW($G:$G)))

同样，旧版Excel需要按Ctrl+Shift+Enter确认，新版自动识别。但要注意文件路径和文件名不能改，否则会报错。

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