多列条件计数并排除重复项的Excel函数问题求解

原公式问题 #

原公式=SUMPRODUCT(--(A:A<>""), --(C:C<>""), --(B:B="activity1"), 1/COUNTIF(A:A, A:A))的核心问题是：COUNTIF(A:A, A:A)对整个A列统计重复值，未结合B、C列的筛选条件。比如A列的100在B列为activity2的行也存在，会导致1/COUNTIF(A:A, A:A)的结果被错误稀释，最终SUMPRODUCT求和后返回0。

正确公式方案 #

方案1：Excel 365/2021 动态数组公式 #

逻辑直观，利用动态数组函数组合实现：

=COUNTA(UNIQUE(FILTER(A:A, (B:B="activity1")*(C:C=1))))

• FILTER(A:A, (B:B="activity1")*(C:C=1))：筛选出B列为activity1且C列为1的所有A列值
• UNIQUE(...)：提取上述结果中的不重复值
• COUNTA(...)：统计不重复值的数量

方案2：兼容旧版Excel的SUMPRODUCT公式 #

通过COUNTIFS把计数范围限定在符合条件的行内：

=SUMPRODUCT(--((B:B="activity1")*(C:C=1)), 1/COUNTIFS(A:A, A:A, B:B, "activity1", C:C, 1))

• --((B:B="activity1")*(C:C=1))：标记符合条件的行（1为符合，0为不符合）
• COUNTIFS(A:A, A:A, B:B, "activity1", C:C, 1)：对每个A列值，仅统计其在符合B、C条件行中的出现次数
• 1/COUNTIFS(...)：将重复值转换为分数（如出现3次的100会变成1/3，3个该分数相加为1），SUMPRODUCT求和后得到不重复区域的数量

验证结果 #

用你提供的表格测试：

两个公式均返回正确结果2（对应100、200两个不重复区域）。

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