Excel公式需求：统计含I.A且排除仅含II.A的Red行数量

需求说明 #

给定如下表格：

需要统计A列为"Red"且B列至少包含一个独立的"I.A"开头条目的行数，正确结果为2。要求：

• 排除B列仅含"II.A"类条目、无"I.A"的Red行（如表格第5行）
• 同时包含"I.A"和"II.A"的Red行需计入统计（如表格第4行）

问题分析 #

• 直接使用COUNTIFS(A:A, "Red", B:B, "*I.A*")会错误统计含"II.A"的行，因为"II.A"字符串中包含"I.A"子串
• 此前的=SUM(IF(A:A="Red", IF(ISNUMBER(FIND("I.A", B:B)) * NOT(ISNUMBER(FIND("II.A", B:B))), 1, 0)))会错误排除同时含"I.A"和"II.A"的行

正确公式 #

通用版（兼容所有Excel版本） #

=SUM(IF((A:A="Red")*ISNUMBER(FIND(", I.A", ", "&B:B&", ")), 1, 0))

注：输入完成后需按Ctrl+Shift+Enter触发数组公式（Excel 365/2021及以后版本无需此操作）

Excel 365/2021简洁版 #

=COUNT(IF((A:A="Red")*ISNUMBER(FIND(", I.A", ", "&B:B&", ")), 1))

公式原理 #

通过给B列内容前后拼接", "，将每个条目都包裹在", "中，再查找", I.A，确保匹配的是独立的"I.A"开头条目，而非"II.A"中的子串：

• 例如B列内容"II.A.1, I.A.2, I.B.2"处理后变为", II.A.1, I.A.2, I.B.2, "，其中包含", I.A，会被判定为符合条件
• B列内容"I.C.1, II.A.2"处理后变为", I.C.1, II.A.2, "，仅包含", II.A，不会被判定为符合条件

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