多列动态SUMIFS优化：按多条件计算指定月份YTD预算总额

核心公式（双条件+动态月份） #

针对按业务单元+活动（多值支持）、预算项筛选，动态计算当前月份YTD累计的需求，替代冗长的多SUMIFS叠加公式，可使用以下轻量化公式：

=SUM(SUMIFS(INDEX(BudgetData!C:N,,1):INDEX(BudgetData!C:N,,XLOOKUP(CurrentMonth,BudgetData!C1:N1,SEQUENCE(12))),BudgetData!A:A,Criteria1,BudgetData!B:B,Criteria2))

参数说明 #

• BudgetData：你的预算表工作表名称，按需修改
• CurrentMonth：存放当前月份的单元格（如G1，值为"Jan"/"Feb"等）
• Criteria1：业务单元+活动的筛选条件，支持多值（如{"ZWE | Maintenance", "Maintenance | Cleaning"}）
• Criteria2：指定预算项的筛选条件（如B24）

公式逻辑 #

1. XLOOKUP(...)：自动匹配当前月份对应的列序号（如"Apr"对应第4列）
2. INDEX(...)：动态生成从1月到当前月份的YTD数据区域，无需手动指定列范围
3. SUMIFS：按多条件筛选数据，因Criteria1为数组，会返回每个条件的累计值，外层SUM汇总所有符合条件的结果

进阶版（支持第三个筛选条件） #

如果需要添加第三个筛选维度（如成本类型、部门等），只需在SUMIFS中追加一组条件对即可：

=SUM(SUMIFS(INDEX(BudgetData!C:N,,1):INDEX(BudgetData!C:N,,XLOOKUP(CurrentMonth,BudgetData!C1:N1,SEQUENCE(12))),BudgetData!A:A,Criteria1,BudgetData!B:B,Criteria2,BudgetData!D:D,Criteria3))

• BudgetData!D:D：第三个条件对应的列（按需修改）
• Criteria3：第三个筛选条件的值

封装为自定义函数（重复使用更高效） #

若需频繁使用该计算，可通过LAMBDA封装为自定义函数，简化调用：

1. 打开Excel的「名称管理器」，新建名称：
   • 名称：YTDBudgetSum
   • 引用位置：

     =LAMBDA(CurrentMonth, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2, [CriteriaRange3], [Criteria3], 
       LET(
         MonthNum, XLOOKUP(CurrentMonth, BudgetData!C1:N1, SEQUENCE(12)),
         YTDRange, INDEX(BudgetData!C:N,,1):INDEX(BudgetData!C:N,,MonthNum),
         SumResult, IF(AND(NOT(ISOMITTED(CriteriaRange3)), NOT(ISOMITTED(Criteria3))), 
           SUMIFS(YTDRange, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2, CriteriaRange3, Criteria3),
           SUMIFS(YTDRange, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2)
         ),
         SUM(SumResult)
       )
     )
     

2. 使用时直接调用：

   // 双条件调用
   =YTDBudgetSum(G1, BudgetData!A:A, {"ZWE | Maintenance", "Maintenance | Cleaning"}, BudgetData!B:B, B24)
   
   // 三条件调用
   =YTDBudgetSum(G1, BudgetData!A:A, {"ZWE | Maintenance", "Maintenance | Cleaning"}, BudgetData!B:B, B24, BudgetData!D:D, "Direct Cost")
   

优势对比 #

• 替代原公式的多SUMIFS叠加，减少公式冗余，降低工作簿体积
• 动态适配当前月份，无需手动调整列范围
• 原生支持多值条件筛选，无需额外嵌套
• 自定义函数封装后，重复使用更高效，降低出错概率

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