如何对含公式的列进行分类汇总并忽略未返回结果的公式？

问题分析 #

你用=IFNA(VLOOKUP(A9,'STRUCTURAL E3D DATA'!G$3:J$2000,3,FALSE),"")填充的列，所有单元格都包含公式，其中部分返回空文本""，部分返回有效数据。现有两个公式的问题：

• =SUBTOTAL(3,L5:L16282)：SUBTOTAL(3)会把空文本""判定为非空，因此所有带公式的单元格（哪怕返回空）都会被统计。
• =SUMPRODUCT((NOT(ISFORMULA(J5:J2000)))*(J5:J2000<>""))：因为所有单元格都有公式，NOT(ISFORMULA(...))全为FALSE，最终结果为0，完全忽略了有效数据。

解决方案 #

1. 统计全范围非空公式结果（不考虑筛选） #

直接判断单元格返回的内容是否不为空，不管是否为公式：

=SUMPRODUCT(--(J5:J2000<>""))

• 原理：J5:J2000<>""逐个检查单元格内容，返回有效数据的为TRUE，返回空文本的为FALSE；--将布尔值转换为1或0；SUMPRODUCT对这些数值求和，得到非空单元格的数量。

2. 统计可见非空公式结果（支持筛选/分类汇总隐藏行） #

如果需要仅统计分类汇总后可见的非空单元格，使用以下公式：

=SUMPRODUCT((SUBTOTAL(103,OFFSET(J5,ROW(J5:J2000)-ROW(J5),0,1)))*(J5:J2000<>""))

• 原理：
  • OFFSET(J5,ROW(J5:J2000)-ROW(J5),0,1)逐个定位到范围内的每个单元格；
  • SUBTOTAL(103,...)用103参数忽略隐藏行，对单个单元格计数（可见则返回1，隐藏则返回0）；
  • 再乘以J5:J2000<>""的非空判断，最终求和得到可见的非空单元格数量。

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