如何在跨月发薪周期中用SUMIF计算月度账单金额？

核心问题分析 #

你之前的月度账单判断逻辑仅对比日期的「日」数值，当发薪周期跨月时（如1/26/2024-2/9/2024），结束日的日数（9）小于起始日的日数（26），导致DAY(E:E)>=DAY(I3) AND DAY(E:E)<DAY(I4)的条件永远不成立，漏计了落在跨月区间内的月度账单（如每月1号到期的房租）。

修正后的完整公式（兼容所有Excel版本） #

将J列的公式替换为以下内容（换行仅为提升可读性，可直接复制使用）：

=SUMIFS(C:C,A:A,"W",F:F,"<>X")*2 + 
 SUMIFS(C:C,A:A,"B",F:F,"<>X") + 
 SUMPRODUCT((A:A="M")*(F:F<>"X")*(
    (DATE(YEAR(I3),MONTH(I3),E:E)>=I3)*(DATE(YEAR(I3),MONTH(I3),E:E)<I4) + 
    (MONTH(I3)<>MONTH(I4))*(DATE(YEAR(I3),MONTH(I3)+1,E:E)>=I3)*(DATE(YEAR(I3),MONTH(I3)+1,E:E)<I4)
 )*C:C) + 
 SUMIFS(C:C,A:A,"Y",E:E,">="&I3,E:E,"<"&I4,F:F,"<>X")

公式各部分详解 #

1. 周度账单（Weekly）：
   保持原逻辑，每两周发薪周期包含2周，所以周度账单金额乘以2：SUMIFS(C:C,A:A,"W",F:F,"<>X")*2

2. 双周账单（Bi-weekly）：
   与发薪周期频率一致，直接求和符合条件的账单：SUMIFS(C:C,A:A,"B",F:F,"<>X")

3. 月度账单（Monthly）：
   核心修正逻辑：判断账单到期日对应的实际日期是否落在当前发薪周期（I3到I4-1）内，而非仅对比日数值：

   • (A:A="M")*(F:F<>"X")：筛选月度且不使用信用卡支付的账单
   • 第一组条件：判断账单在发薪周期起始月的到期日是否在区间内
   • 第二组条件：仅当发薪周期跨月时，判断账单在次月的到期日是否在区间内（比如1/26-2/9的周期，会检查2月1日是否在区间内）
   • 用SUMPRODUCT实现数组判断，兼容旧版Excel

4. 年度账单（Yearly）：
   保持原逻辑，直接判断年度账单的到期日是否落在当前发薪周期内：SUMIFS(C:C,A:A,"Y",E:E,">="&I3,E:E,"<"&I4,F:F,"<>X")

Excel 365/2021简化版（可选） #

如果使用新版Excel，可利用动态数组和LET函数简化公式，提升可读性：

=LET(
    startDate, I3,
    endDate, I4,
    weeklyAmt, SUMIFS(C:C,A:A,"W",F:F,"<>X")*2,
    biWeeklyAmt, SUMIFS(C:C,A:A,"B",F:F,"<>X"),
    monthlyAmt, SUM(IF((A:A="M")*(F:F<>"X"), IF(
        OR(
            DATE(YEAR(startDate),MONTH(startDate),E:E)>=startDate,
            (MONTH(startDate)<>MONTH(endDate))*(DATE(YEAR(startDate),MONTH(startDate)+1,E:E)>=startDate)
        )*(DATE(YEAR(startDate),MONTH(startDate)+(MONTH(startDate)<>MONTH(endDate)),E:E)<endDate),
        C:C,0
    ),0)),
    yearlyAmt, SUMIFS(C:C,A:A,"Y",E:E,">="&startDate,E:E,"<"&endDate,F:F,"<>X"),
    weeklyAmt + biWeeklyAmt + monthlyAmt + yearlyAmt
)

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