动态Excel工作表中指定行COUNTIF跨表求和问题求助

• 需求背景：需要在动态数量的Excel工作表（对应名为“Months”的列表中的月份工作表，当前范围为Nov 2023至Feb 2024，每月会新增工作表）中，统计每位团队成员出现“Training”的总次数。
• 示例数据：以团队成员A7为例，各月统计结果为：Nov 23 - 2、Dec 23 - 1、Jan 24 - 1、Feb 24 - 1，总计应为5。
• 当前问题：使用的公式仅返回第一个工作表（Nov 23）的结果2，原公式如下：

=SUMPRODUCT(COUNTIF(INDEX(INDIRECT("'"&Months&"'!$A$1:$Z$20"),MATCH(A7,INDIRECT("'"&Months&"'!$A:$A"),0),0),"Training"))

修正方案 #

原因分析 #

原公式中，MATCH(A7, INDIRECT("'"&Months&"'!$A:$A"),0)仅会返回第一个工作表中成员所在的行号，无法对Months列表中的每个工作表单独执行匹配，导致仅统计了第一个工作表的数据。

1. 适用于Excel 365/2021（支持动态数组与LAMBDA）的公式 #

=SUM(MAP(Months, LAMBDA(sheet, COUNTIF(INDEX(INDIRECT("'"&sheet&"'!$A$1:$Z$20"),MATCH(A7,INDIRECT("'"&sheet&"'!$A:$A"),0),0),"Training"))))

• 逻辑说明：
  • MAP(Months, LAMBDA(sheet, ...))遍历Months列表中的每个工作表名称
  • 对每个工作表，通过INDEX+MATCH精准定位成员A7所在的行
  • 用COUNTIF统计该行内“Training”的出现次数
  • 最后用SUM汇总所有工作表的统计结果

2. 适用于旧版Excel（无动态数组支持）的数组公式 #

需按Ctrl+Shift+Enter组合键输入（而非直接回车）：

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Months&"'!"&"R"&MATCH(A7,INDIRECT("'"&Months&"'!$A:$A"),0)&"C1:R"&MATCH(A7,INDIRECT("'"&Months&"'!$A:$A"),0)&"C26",FALSE),"Training"))

• 逻辑说明：
  • 用R1C1单元格格式为每个工作表生成成员所在行的区域（C1对应A列，C26对应Z列）
  • INDIRECT调用每个工作表的目标行区域，COUNTIF分别统计次数
  • SUMPRODUCT汇总所有工作表的统计结果

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