Excel：统计A列等于Y时，值X出现的行数

我有一个约3000行的数据集，数据板块重复但值略有差异：A列是固定重复的颜色，B:G列的值随颜色变化。
需要统计每个颜色下，指定值（如Oscar）出现的行数：比如示例数据里Red对应2行，Oscar只在1行出现，统计值为1；Purple对应2行，Oscar在2行都出现，统计值为2。

当前使用的公式会统计该值在对应颜色下的出现次数，而非行数：

=COUNT(IF(FILTER($A:$G,$K22=$A:$A)=Q$21,1,""))

数据集示例 #

方法1：BYROW + FILTER + COUNT（适用于Excel 365/2021） #

先筛选对应颜色的行，逐行检查是否包含指定值，最后统计符合条件的行数：

=COUNT(BYROW(FILTER($B:$G,$A:$A=K22),LAMBDA(row,IF(COUNTIF(row,Q$21)>0,1,""))))

• FILTER($B:$G,$A:$A=K22)：提取A列等于目标颜色的所有B:G列数据
• BYROW(..., LAMBDA(row,IF(COUNTIF(row,Q$21)>0,1,"")))：对每一行判断是否存在指定值，存在返回1，否则返回空
• COUNT(...)：统计返回1的数量，即符合条件的行数

方法2：SUMPRODUCT（兼容所有Excel版本） #

如果不支持动态数组函数，用SUMPRODUCT更稳妥：

=SUMPRODUCT(--($A:$A=K22),--(MMULT(--($B:$G=Q$21),ROW($B$1:$G$1)^0)>0))

• --($A:$A=K22)：将A列匹配目标颜色的行转为1，否则0
• --($B:$G=Q$21)：将B:G列匹配指定值的单元格转为1，否则0
• MMULT(...,ROW($B$1:$G$1)^0)：对每一行求和，判断该行是否至少有一个指定值（结果>0表示存在）
• SUMPRODUCT：将两个数组相乘后求和，得到符合条件的行数

方法3：COUNTIFS + INDIRECT（另一种兼容方案） #

=COUNTIFS($A:$A,K22,INDIRECT("R"&ROW($A:$A)&"C2:C7",FALSE),"*"&Q$21&"*")

• INDIRECT("R"&ROW($A:$A)&"C2:C7",FALSE)：引用每行的B:G列区域
• COUNTIFS：统计A列等于目标颜色，且该行包含指定值的行数

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