SUMPRODUCT公式无法识别B列重复员工的M列值差异，求排查方案

一、排查数据格式问题 #

原公式=SUMPRODUCT(--(B:B=B1),--(M:M<>M1))失效最常见的原因是数据存在隐形格式差异：

• B列员工姓名可能包含前后空格、全角/半角空格，导致B:B=B1匹配失败
• M列内容可能存在隐藏字符（比如换行符、非打印字符），看起来相同实际不相等

解决办法：用TRIM清除空格、CLEAN清除非打印字符，修改公式为：

=SUMPRODUCT(--(TRIM(B:B)=TRIM(B1)),--(CLEAN(M:M)<>CLEAN(M1)))

二、优化公式引用范围 #

整列引用B:B和M:M会大幅增加计算量，数据量大时可能导致计算异常甚至返回错误。

解决办法：改用实际数据的有限范围，并用绝对引用锁定范围（避免下拉时偏移），比如数据到第1000行：

=SUMPRODUCT(--(B$2:B$1000=B1),--(M$2:M$1000<>M1))

三、换用更直观的替代公式 #

如果只需判断是否存在差异而非统计差异行数，用COUNTIFS更简洁直观，返回TRUE表示有差异，FALSE表示无差异：

=COUNTIFS(B:B,TRIM(B1),M:M,"<>"&CLEAN(M1))>0

四、处理空值特殊情况 #

如果M列存在空单元格，原公式会把空值和非空值判定为差异。若需要排除空值干扰，可添加空值过滤条件：

=SUMPRODUCT(--(B:B=B1),--(M:M<>M1),--(M:M<>""),--(M1<>""))

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