Google Sheets条件格式公式无效：SUMPRODUCT公式无法通过验证

你试图用公式 =SUMPRODUCT(--ISNUMBER(SEARCH(AT2,workpattern)))>0 检查单元格AT2是否包含命名区域workpattern中的合法值，想要在AT2不属于合法值时标红，但该公式在条件格式中被判定无效。以下是修正方案：

问题根源 #

原公式的核心问题是SEARCH的参数顺序逻辑错误：你用AT2去匹配workpattern的每个值，不符合“检查AT2是否包含任一合法值”的逻辑；同时条件格式对数组运算的引用方式有特殊要求，容易触发无效判定。

修正公式 #

根据你的需求选择对应公式：

需求1：AT2完全等于workpattern中的某一合法值 #

使用公式：

=NOT(COUNTIF(workpattern, AT2))

• 逻辑：COUNTIF(workpattern, AT2)统计workpattern中与AT2完全匹配的数量，NOT取反后，当数量为0（即无匹配）时返回TRUE，触发标红格式。

需求2：AT2包含workpattern中的任一合法值 #

使用公式：

=NOT(SUMPRODUCT(--ISNUMBER(SEARCH(workpattern, AT2))))

• 逻辑：将SEARCH参数反转，用workpattern中的每个值去搜索AT2，--ISNUMBER将搜索结果转为0或1，SUMPRODUCT求和得到匹配次数；NOT取反后，当无匹配时返回TRUE，触发标红格式。

条件格式设置步骤 #

1. 选中需要应用格式的目标单元格（如AT2，或整个AT列）
2. 打开「条件格式」→「新建规则」→ 选择「使用公式确定要设置格式的单元格」
3. 粘贴上述修正后的公式
4. 点击「格式」按钮，设置字体为红色（或单元格填充红色）
5. 确认并应用规则

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