改写COUNTIFS函数以适配动态数组条件

需求说明 #

需要从数据集中统计同时满足以下两个条件的姓名数量：

• 该姓名存在“诊断2”的记录
• 该姓名存在“药物A”的记录

核心思路是使用带数组条件的COUNTIFS函数，但需先解决生成符合“诊断2”的姓名数组的问题。

目标公式（单元格F6） #

原公式框架：

=COUNTIFS(Criteria,"Drug",Value,"A",Names,ArrayOfNamesWithDiagnosis2)

参数对应：

• Criteria = A2:A12（条件类型列）
• Value = B2:B12（条件值列）
• Names = C2:C12（姓名列）
• ArrayOfNamesWithDiagnosis2 = 所有符合“诊断2”的姓名构成的数组

具体实现 #

方法1（支持动态数组的Excel版本） #

用FILTER函数直接提取符合“诊断2”的姓名数组，代入COUNTIFS得到最终公式：

=COUNTIFS(A2:A12,"Drug",B2:B12,"A",C2:C12,FILTER(C2:C12,(A2:A12="Diagnosis")*(B2:B12="2")))

方法2（旧版Excel，需按Ctrl+Shift+Enter确认数组公式） #

用IF函数生成符合条件的姓名数组，替代FILTER：

=COUNTIFS(A2:A12,"Drug",B2:B12,"A",C2:C12,IF((A2:A12="Diagnosis")*(B2:B12="2"),C2:C12))

示例数据与预期结果 #

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