Excel按产品组统计符合条件的不同日期区间未结保险理赔数量求助

你的需求不需要组合VLOOKUP和COUNTIF，直接用Excel自带的多条件计数函数COUNTIFS即可快速实现，具体操作如下：

前提约定 #

• 原始数据工作表命名为「数据源」，列结构如下：
  • A列：Product Group（产品组）
  • B列：First notification date（首次通知日期）
  • C列：Status（状态）
• 统计结果工作表的A列为待统计的产品组列表，B列表头为「小于1年」，C列表头为「1-3年」，D列表头为「大于3年」
• 时间区间默认以当前系统日期为基准，如需固定基准日可自行调整公式参数

公式编写（以结果表第二行为例，对应第一个待统计产品组） #

1. 小于1年未结理赔数（B2单元格） #

输入公式：
=COUNTIFS(数据源!$A:$A,$A2,数据源!$C:$C,"<>CLOSE",数据源!$B:$B,">"&TODAY()-365)

2. 1-3年未结理赔数（C2单元格） #

输入公式：
=COUNTIFS(数据源!$A:$A,$A2,数据源!$C:$C,"<>CLOSE",数据源!$B:$B,"<="&TODAY()-365,数据源!$B:$B,">"&TODAY()-1095)

3. 大于3年未结理赔数（D2单元格） #

输入公式：
=COUNTIFS(数据源!$A:$A,$A2,数据源!$C:$C,"<>CLOSE",数据源!$B:$B,"<="&TODAY()-1095)

使用说明 #

• 写完B2、C2、D2的公式后，选中三个单元格向下拖动填充，即可自动完成所有产品组的统计
• 公式中<>CLOSE条件会自动匹配状态为OPEN、REOPEN的记录，不需要单独写两个状态的判断
• 如果需要使用固定日期作为统计基准，将公式中的TODAY()替换为对应日期单元格引用即可，比如基准日存放在结果表F1单元格，就替换为$F$1
• 请先确认数据源的「首次通知日期」列是Excel可识别的日期格式，若为文本格式需要先转换为日期格式再使用公式
• 如果需要更精准的年月匹配（不受闰年、每月天数差异影响），可以将TODAY()-365替换为EDATE(TODAY(),-12)，TODAY()-1095替换为EDATE(TODAY(),-36)，EDATE函数直接计算指定月数之前的日期，更符合按年统计的实际需求

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