Google Sheets中如何结合筛选视图与条件格式使用countif()？

核心问题 #

直接使用COUNTIF()会统计全部数据，无视筛选后的隐藏行，同时需要适配后续条件格式的文本修改需求。

方案1：无辅助列，直接联动条件格式规则 #

假设数据范围为A2:A100，条件格式规则为单元格包含特定文本则标红，使用以下公式统计可见的符合条件单元格：

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),--(ISNUMBER(SEARCH("Tree Logs",A2:A100))))

公式拆解 #

• SUBTOTAL(103, OFFSET(...))：103代表忽略筛选隐藏行的COUNTA功能，OFFSET逐个定位每行单元格，返回1（行可见）或0（行隐藏）
• --(ISNUMBER(SEARCH("Tree Logs",A2:A100)))：判断单元格是否包含目标文本，返回1（符合条件）或0（不符合条件）
• SUMPRODUCT：将两个数组对应元素相乘后求和，最终得到可见且符合条件的单元格数量

适配条件修改 #

后续需要将目标文本从Tree Logs改为Branches时，直接修改公式中的"Tree Logs"为"Branches"，与条件格式规则保持一致即可。

方案2：辅助列实现一键修改（更适合频繁调整条件） #

如果需要频繁修改目标文本，推荐用辅助列联动，避免重复修改多个位置：

1. 设置辅助列：在B2单元格输入公式并下拉填充至数据末尾：

   =ISNUMBER(SEARCH($C$1,A2))
   

   其中C1为专门存放目标文本的单元格（比如在C1输入Tree Logs或Branches）
2. 同步条件格式规则：修改条件格式规则为=B2=1，设置标红格式
3. 统计可见数据：使用以下公式统计结果：

   =SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),--(B2:B100=1))
   

适配条件修改 #

后续只需修改C1单元格的文本，辅助列、条件格式、统计结果会自动同步更新，无需调整任何公式。

注意事项 #

• SUBTOTAL(103)会同时忽略筛选隐藏行和手动隐藏行，如果仅需忽略筛选隐藏行，可将参数改为3
• SEARCH不区分大小写，若需严格区分大小写，替换为FIND函数即可

内容的提问来源于stack exchange，提问作者Marc Anthony Manfredy