Excel：按列条件统计文本在多行中的出现总次数

原始数据集 #

需求说明 #

需要按姓名统计物品在整张表格中的出现次数，例如：

• James的Apple出现2次
• James的Orange出现1次
• Thomas的Orange出现3次
• Peter的Banana出现2次

已尝试的方法及问题 #

1. 使用公式 =COUNTIF(INDEX(B:D,MATCH("Thomas",A:A,0),0),"Orange")：仅返回符合姓名条件的第一行中物品的计数，无法覆盖所有行，比如Thomas的Orange只会返回1，实际应为3次。
2. 使用常规COUNTIFS公式 =COUNTIFS(A2:A7,"Thomas",B2:D7,"Orange")：因COUNTIFS仅支持单列匹配多条件，会返回值错误。
3. 分列统计再求和：对B/C/D列分别用COUNTIFS后求和，虽可行但数据集大时操作繁琐，示例如下：

单单元格直接统计的公式方案 #

方案1：SUMPRODUCT数组公式（兼容旧版Excel） #

若要统计指定姓名（如H2单元格的姓名）和指定物品（如I2单元格的物品）的总次数，可使用：

=SUMPRODUCT((A$2:A$7=H2)*(B$2:D$7=I2))

原理：通过数组运算，先判断每行姓名是否匹配，再判断每行B/D列的物品是否匹配，最后将所有符合条件的结果求和，得到总次数。

方案2：COUNTIFS+TEXTJOIN（适用于Excel 365/2021及以上） #

用TEXTJOIN合并对应姓名的所有物品为字符串，再用COUNTIF统计出现次数：

=COUNTIF(TEXTJOIN(",",TRUE,IF(A$2:A$7=H2,B$2:D$7,"")),I2)

注意：旧版Excel需按Ctrl+Shift+Enter确认数组公式，Excel 365/2021可自动溢出计算。

方案3：动态数组公式（Excel 365专属） #

一次性生成所有姓名+物品的统计结果：

=LET(
    names,A2:A7,
    items,B2:D7,
    flatNames,TOCOL(IF(items<>"",names,"")),
    flatItems,TOCOL(items,1),
    uniquePairs,UNIQUE(HSTACK(flatNames,flatItems)),
    counts,COUNTIFS(flatNames,INDEX(uniquePairs,,1),flatItems,INDEX(uniquePairs,,2)),
    HSTACK(uniquePairs,counts)
)

该公式会自动提取所有非空物品对应的姓名，生成唯一的姓名-物品对，并统计每对的出现次数，直接输出完整统计表格。

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