Excel按姓名和月份统计指定活动次数及COUNTIFS报错解决

错误原因分析 #

原公式返回#VALUE!错误的核心问题有两点：

1. COUNTIFS函数要求所有条件区域的行列维度完全一致，原公式使用的条件区域分别为9行1列、1行6列、9行6列，维度不匹配触发报错。
2. 原公式误将第一列表头单元格A1（内容为姓名，不属于日期字段）纳入日期筛选范围，即使修正维度问题也会导致统计结果偏差。

通用可拖拽填充公式 #

全版本Excel兼容、支持横竖拖拽填充的正确公式如下：

=SUMPRODUCT(($A$2:$A$9=$A15)*($B$1:$F$1>=B$14)*($B$1:$F$1<C$14)*($B$2:$F$9=$F$14))

公式引用规则适配拖拽需求：

• 姓名匹配区域$A$2:$A$9、日期区域$B$1:$F$1、活动内容区域$B$2:$F$9、活动选择单元格$F$14均为绝对引用，拖拽时不会偏移
• 当前行姓名匹配$A15锁列不锁行，向下拖拽时自动匹配对应行的姓名
• 月份起止日期匹配B$14、C$14锁行不锁列，向右拖拽时自动匹配对应列的月份范围

其他实现方案 #

• 数据透视表（无公式首选）：将原始数据导入透视表，行字段选人员姓名，列字段选日期并设置按月份分组，筛选器字段选活动类型，值字段设置为计数。如需切换统计的活动类型，直接在筛选器选择即可，也可插入活动类型切片器实现一键切换，无需修改公式。
• Power Query（大数据量首选）：将原始数据加载到Power Query编辑器，选中姓名列执行「逆透视其他列」，得到姓名、日期、活动类型三列结构化数据，新增列提取日期对应的月份，按姓名、月份、活动类型分组聚合计数，加载回工作表后可直接筛选查看对应活动的统计结果，数据更新时只需右键刷新即可。
• 动态数组公式（Excel 365/2021+适用）：可读性更强的动态数组写法如下：

  =COUNTA(FILTER($B$2:$F$9,($A$2:$A$9=$A15)*($B$1:$F$1>=B$14)*($B$1:$F$1<C$14)*($B$2:$F$9=$F$14),""))
  

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