基于另一表格位置匹配并求和目标表格数值的公式需求

问题背景 #

我正在跟踪测验结果，想按多个类别分析表现。现有三个表格标签：

• Results：按日期记录每题答题结果（1为正确，0为错误）
• Questions：按日期记录每题所属类别
• Categories：需要列出每个类别、该类别题目出现总次数及答对总次数，目前仅答对总次数无法实现，试过INDEX/MATCH、FILTER、QUERY等函数组合，没法基于类别和日期确定题目位置，匹配对应结果并求和。

示例数据 #

Results标签数据：

DATE    Q1 Q2 Q3 Q4
1/1/23   1  0  1  1
1/2/23   1  0  1  0
1/3/23   0  1  1  1

Questions标签数据：

DATE    Q1       Q2       Q3         Q4
1/1/23  History  Animals  Sports     Music
1/2/23  Sports   Music    Geography  Movies
1/3/23  Movies   Music    Movies     History

Categories标签目标效果：

CATEGORY   COUNT  TIMES CORRECT
Animals    1      0
Geography  1      1
History    2      1
Movies     3      2
Music      3      1
Sports     2      2

解决方案 #

在Categories标签的「TIMES CORRECT」列（假设对应C列，A列为类别名称），对每个类别单元格（比如C2）输入以下公式：

=SUMPRODUCT((Questions!$B$2:$E$4=A2)*(Results!$B$2:$E$4=1))

按回车后下拉填充到所有类别行即可。

公式说明：

• Questions!$B$2:$E$4=A2：遍历Questions标签的所有题目类别区域，标记出等于当前类别（A2）的单元格（运算时TRUE/FALSE自动转为1/0）
• Results!$B$2:$E$4=1：同时标记Results标签对应位置答对的单元格（同样转为1/0）
• SUMPRODUCT会把两个区域的对应值相乘后求和，最终得到该类别所有答对题目的总数

补充：COUNT列公式（如果还没设置）

如果「COUNT」列（B列）还没实现，可在B2输入：

=COUNTIF(Questions!$B$2:$E$4,A2)

下拉填充即可统计每个类别的题目出现次数。

动态数组版本（一次性生成所有结果）

如果你的Google Sheets支持动态数组函数，可在C2输入以下公式，自动生成所有类别的答对次数：

=BYROW(A2:A7,LAMBDA(cat,SUMPRODUCT((Questions!B2:E4=cat)*(Results!B2:E4=1))))

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