如何使用SUMIFS函数按频率计算周期性付款

原公式问题分析 #

你的SUMIFS公式出错原因在于：

• SUMIFS不支持动态生成的数组作为条件区域（比如DATE(YEAR(Plan!B$1),MONTH(Plan!B$1),DAY(List!D1:D6))这种数组无法被SUMIFS正确识别）
• 日期判断逻辑错误，没有考虑周期性付款的递推规则，只是简单拼接年月和付款日，会出现月末日期不匹配的问题

替代方案：用SUMPRODUCT处理数组判断 #

SUMPRODUCT支持数组运算，能灵活处理周期性付款的日期匹配逻辑，以下分频率给出可拖拽的公式：

1. 月度付款（Monthly）统计公式 #

在Plan!C3单元格输入以下公式，可横向/纵向拖拽：

=SUMPRODUCT(
    --(List!$B$1:$B$6=Plan!$A3),
    --(List!$C$1:$C$6="Monthly"),
    --(
        DATEDIF(List!$D$1:$D$6, Plan!C$1, "m") < DATEDIF(List!$D$1:$D$6, Plan!D$1, "m")
        OR (
            DAY(List!$D$1:$D$6) <= DAY(Plan!D$1)
            AND DATEDIF(List!$D$1:$D$6, Plan!D$1, "m") = DATEDIF(List!$D$1:$D$6, Plan!C$1, "m")
        )
    ),
    List!$E$1:$E$6
)

逻辑说明：

• 匹配对应类别和月度付款频率
• 判断当前周是否包含月度付款日：要么周跨月（起止日期分属不同月份），要么周在同一个月内且付款日≤周结束日

2. 双月付款（Bi-monthly）统计公式 #

=SUMPRODUCT(
    --(List!$B$1:$B$6=Plan!$A3),
    --(List!$C$1:$C$6="Bi-monthly"),
    --(
        MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 2) = 0
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 2), DAY(List!$D$1:$D$6)) >= Plan!C$1
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 2), DAY(List!$D$1:$D$6)) <= Plan!D$1
    ),
    List!$E$1:$E$6
)

逻辑说明：

• 计算最近付款日到周结束日的月份差，取模2等于0说明当前处于双月付款周期
• 生成该周期的付款日期，判断是否落在当前周区间内

3. 季度付款（Quarterly）统计公式 #

=SUMPRODUCT(
    --(List!$B$1:$B$6=Plan!$A3),
    --(List!$C$1:$C$6="Quarterly"),
    --(
        MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 3) = 0
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 3), DAY(List!$D$1:$D$6)) >= Plan!C$1
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 3), DAY(List!$D$1:$D$6)) <= Plan!D$1
    ),
    List!$E$1:$E$6
)

逻辑说明：

• 类似双月逻辑，月份差取模3等于0说明处于季度付款周期
• 验证该周期付款日期是否在当前周内

合并所有频率的公式 #

如果需要在一个单元格内统计所有频率的付款总额，可将三个SUMPRODUCT公式相加：

=SUMPRODUCT(
    --(List!$B$1:$B$6=Plan!$A3),
    --(List!$C$1:$C$6="Monthly"),
    --(
        DATEDIF(List!$D$1:$D$6, Plan!C$1, "m") < DATEDIF(List!$D$1:$D$6, Plan!D$1, "m")
        OR (
            DAY(List!$D$1:$D$6) <= DAY(Plan!D$1)
            AND DATEDIF(List!$D$1:$D$6, Plan!D$1, "m") = DATEDIF(List!$D$1:$D$6, Plan!C$1, "m")
        )
    ),
    List!$E$1:$E$6
)
+
SUMPRODUCT(
    --(List!$B$1:$B$6=Plan!$A3),
    --(List!$C$1:$C$6="Bi-monthly"),
    --(
        MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 2) = 0
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 2), DAY(List!$D$1:$D$6)) >= Plan!C$1
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 2), DAY(List!$D$1:$D$6)) <= Plan!D$1
    ),
    List!$E$1:$E$6
)
+
SUMPRODUCT(
    --(List!$B$1:$B$6=Plan!$A3),
    --(List!$C$1:$C$6="Quarterly"),
    --(
        MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 3) = 0
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 3), DAY(List!$D$1:$D$6)) >= Plan!C$1
        AND DATE(YEAR(Plan!D$1), MONTH(Plan!D$1)-MOD(DATEDIF(List!$D$1:$D$6, Plan!D$1, "m"), 3), DAY(List!$D$1:$D$6)) <= Plan!D$1
    ),
    List!$E$1:$E$6
)

注意事项 #

• 把公式中的List!$B$1:$B$6等范围替换为实际的数据源区域
• 若最近付款晚于计划周，DATEDIF会报错，可添加IF(DATEDIF(...)>=0, ..., FALSE)过滤这种情况
• DATE函数会自动处理月末日期（比如31号遇到小月会自动转为月末），符合实际付款逻辑
• 公式使用$锁定了正确的行/列，可直接横向或纵向拖拽复用

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