Google Sheets：按可变日期区间计算指定月份金额总和

核心需求 #

根据第一个表格中每条记录的「开始日期」「结束日期」，判断该记录是否覆盖第二个表格中的目标月份，将所有符合条件的「金额」求和。

假设表格结构 #

• 第一个表格命名为Table1，列顺序：A=描述，B=开始日期，C=结束日期，D=金额
• 第二个表格中，A列为目标月份（格式如01-2024），B列为总计列（存放计算结果）

方法1：使用SUMPRODUCT函数 #

在第二个表格的B2单元格（对应A2的01-2024）输入以下公式，下拉填充即可：

=SUMPRODUCT(
  (Table1!$B:$B <= EOMONTH(DATEVALUE("01-"&A2), 0)) *
  (Table1!$C:$C >= DATEVALUE("01-"&A2)) *
  Table1!$D:$D
)

公式拆解

• DATEVALUE("01-"&A2)：将A列的01-2024文本转换为该月第一天的日期值（如2024/1/1）
• EOMONTH(..., 0)：计算该月份的最后一天（如2024/1/31）
• 第一个条件Table1!$B:$B <= 当月最后一天：确保记录的开始日期不晚于目标月份的月底
• 第二个条件Table1!$C:$C >= 当月第一天：确保记录的结束日期不早于目标月份的月初
• 两个条件相乘表示「同时满足」，再乘以金额列，SUMPRODUCT会自动对符合条件的金额求和

方法2：使用SUMIFS函数 #

如果更习惯SUMIFS，也可以用以下公式，逻辑和SUMPRODUCT一致：

=SUMIFS(
  Table1!$D:$D,
  Table1!$B:$B, "<="&EOMONTH(DATEVALUE("01-"&A2), 0),
  Table1!$C:$C, ">="&DATEVALUE("01-"&A2)
)

关键注意事项 #

1. 日期格式检查：确保Table1中的「开始日期」「结束日期」是Google Sheets的标准日期格式，不是纯文本。如果是文本，需要在公式中用DATEVALUE(Table1!$B:$B)转换，例如：

   =SUMPRODUCT(
     (DATEVALUE(Table1!$B:$B) <= EOMONTH(DATEVALUE("01-"&A2), 0)) *
     (DATEVALUE(Table1!$C:$C) >= DATEVALUE("01-"&A2)) *
     Table1!$D:$D
   )
   

2. 月份列格式适配：如果第二个表格的月份列是日期格式（如单元格实际存储2024/1/1，仅显示为01-2024），可以简化公式，直接用A2代替DATEVALUE("01-"&A2)：

   =SUMPRODUCT(
     (Table1!$B:$B <= EOMONTH(A2, 0)) *
     (Table1!$C:$C >= A2) *
     Table1!$D:$D
   )
   

验证示例 #

• 针对01-2024：First（30）+ Second（10）= 40，符合预期
• 针对02-2024：First（30）+ Second（10）+ Third（15）= 55，符合预期

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