Google Sheets：如何在SUMIFS中排除指定数据优化公式

我用以下公式计算Time Sheet工作表指定日期范围内K列的总工时：

=SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")

该公式可正常运行，但我希望添加筛选条件：在同一日期范围内，排除A列包含特定编码（如PP999、PP998等）的数据。

目前我使用了以下临时解决公式，但公式过长、灵活性差，寻求优化方案：

=SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")-SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!A2:A,"PP999",'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")-SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!A2:A,"PP998",'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")-SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!A2:A,"PP997",'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")

优化方案 #

方案1：SUMIFS+数组排除（兼容多数Excel版本） #

将排除编码整合为数组，直接嵌入SUMIFS条件，大幅缩短公式：

=SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023",'Time Sheet'!A2:A,"<>"&{"PP999","PP998","PP997"})

注意：非Excel 365版本输入后需按Ctrl+Shift+Enter作为数组公式执行

方案2：SUMPRODUCT（兼容性拉满） #

用SUMPRODUCT直接处理多条件逻辑，同时用DATE函数替代文本日期，避免地区格式差异问题：

=SUMPRODUCT(('Time Sheet'!G2:G>=DATE(2022,12,1))*('Time Sheet'!G2:G<=DATE(2023,11,30))*ISNA(MATCH('Time Sheet'!A2:A,{"PP999","PP998","PP997"},0))*'Time Sheet'!K2:K)

方案3：Excel 365/2021专属（动态数组极致灵活） #

用FILTER先筛选符合条件的工时，再求和，可读性更强；还可将排除编码存到单元格区域，后续修改无需调整公式：

=SUM(FILTER('Time Sheet'!K2:K,('Time Sheet'!G2:G>=DATE(2022,12,1))*('Time Sheet'!G2:G<=DATE(2023,11,30))*ISNA(MATCH('Time Sheet'!A2:A,{"PP999","PP998","PP997"},0)),0))

若要更灵活，可把排除编码放在Sheet2!A1:A3区域，公式改为：

=SUM(FILTER('Time Sheet'!K2:K,('Time Sheet'!G2:G>=DATE(2022,12,1))*('Time Sheet'!G2:G<=DATE(2023,11,30))*ISNA(MATCH('Time Sheet'!A2:A,Sheet2!A1:A3,0)),0))

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