MS Excel 2010多条件统计唯一客户数：按区域与日期统计求助

某公司有3个区域，需统计2023年1月1日（对应数字日期20221231）和2023年10月31日（对应数字日期20231031）各区域的唯一客户数量。

原始数据 #

预期结果 #

尝试过的公式 #

=SUM(IF(FREQUENCY(IF($M$2:$M$9097=20221231,$B$2:$B$9097),IF($K$2:$K$9097="Z1",'Raw Data'!$B$2:$B$9097))>0,1))

=SUMPRODUCT(IF(($M$2:$M$9097="20221231")*($K$2:$K$9097="Z1"),1/COUNTIFS($B$2:$B$9097,$B$2:$B$9097,$M$2:$M$9097,20221231,$K$2:$K$9097,"Z1"),0))

解决方案 #

方法1：兼容旧版Excel的SUMPRODUCT公式 #

针对每个区域+日期组合，使用以下公式计算唯一客户数（旧版Excel输入后需按Ctrl+Shift+Enter作为数组公式执行）：

• Z1区域2023年1月1日客户数：

=SUMPRODUCT(($K$2:$K$9097="Z1")*($M$2:$M$9097=20221231)/COUNTIFS($B$2:$B$9097,$B$2:$B$9097,$K$2:$K$9097,"Z1",$M$2:$M$9097,20221231))

• Z2区域2023年10月31日客户数：

=SUMPRODUCT(($K$2:$K$9097="Z2")*($M$2:$M$9097=20231031)/COUNTIFS($B$2:$B$9097,$B$2:$B$9097,$K$2:$K$9097,"Z2",$M$2:$M$9097,20231031))

方法2：Excel 365/2021动态数组公式 #

如果使用支持动态数组的Excel版本，可结合FILTER+UNIQUE+COUNTA实现更简洁的计算，公式可下拉填充：

=COUNTA(UNIQUE(FILTER($B$2:$B$9097,($K$2:$K$9097=A2)*($M$2:$M$9097=20221231))))

（其中A2为存放区域名称的单元格，如Z1；需统计10月31日数据时，将20221231替换为20231031）

原公式错误分析 #

1. 第一个FREQUENCY公式的第二个参数逻辑错误，分组依据应为符合日期+区域条件的客户ID，而非仅区域筛选后的所有客户ID；
2. 第二个SUMPRODUCT公式中，$M$2:$M$9097="20221231"用了文本引号，但处理日期是数字型，应去掉引号直接写20221231。

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