Office 365 Excel中多条件统计混合文本数字列数据的问题求助

方案1：Power Query预处理（推荐，从根源解决） #

在Power Query中给数据集新增一个数值转换列，既保留原混合列，又能用于统计：

1. 打开Power Query编辑器，选中目标数据集
2. 点击「添加列」→「自定义列」
3. 输入自定义公式：

   try Number.From([Value]) otherwise null
   

   该公式会将可转换为数字的内容转为数值，无法转换的文本内容设为null
4. 关闭并加载数据到Excel，此时会新增一列（可命名为Value_Num）
5. 用新列编写COUNTIFS公式：

   =COUNTIFS(Yearly_Data[Medical Year],Processing!$B$32,Yearly_Data[Attribute],Processing!$A$34,Yearly_Data[Value_Num],">100")
   

方案2：Excel中用SUMPRODUCT直接计算 #

无需修改数据源，直接用SUMPRODUCT处理数组转换和多条件统计：

=SUMPRODUCT(
  --(Yearly_Data[Medical Year]=Processing!$B$32),
  --(Yearly_Data[Attribute]=Processing!$A$34),
  --(IFERROR(--Yearly_Data[Value],0)>100)
)

• -- 用于将布尔判断结果（TRUE/FALSE）转换为1/0，方便SUMPRODUCT求和
• IFERROR(--Yearly_Data[Value],0) 将文本内容转为0（避免转换错误），同时将数字文本转为数值
• 三个条件的结果相乘后求和，得到符合所有条件的数量

方案3：用动态数组函数COUNT+FILTER（Office 365专属） #

利用Office 365的动态数组特性，先筛选符合条件的行，再统计数量：

=COUNT(
  FILTER(
    Yearly_Data[Value],
    (Yearly_Data[Medical Year]=Processing!$B$32)*
    (Yearly_Data[Attribute]=Processing!$A$34)*
    ISNUMBER(VALUE(Yearly_Data[Value]))*
    (VALUE(Yearly_Data[Value])>100),
    ""
  )
)

• FILTER 先筛选出满足年份、属性要求，且能转为有效数值、数值大于100的内容
• COUNT 统计筛选结果中的有效数值数量（文本内容会被自动忽略）

为什么之前的公式报错？ #

COUNTIFS 的参数要求是单元格区域，不能是VALUE(FILTER(...))生成的动态数组，因此直接嵌套会返回错误，改用上述支持数组运算的函数即可解决。

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