当前位置:首页 > 科技 > 正文

年少不知SUMPRODUCT好,错把SUMIFS当成宝

与 30万 粉丝一起学Excel

刚学完了多条件统计的2个公式,学员就用在了实际工作中, 按项目、日期统计金额,结果发现最后一个单元格不一样。因此得出结论,第一个公式靠谱,第二个没啥用。
=SUMIFS(C:C,A:A,E2,B:B,F2)
=SUMPRODUCT(($E2=$A$2:$A$32)*($F2=$B$2:$B$32)*$C$2:$C$32)


事实真的如此吗?

卢子打开了表格,仔细查看了一下,发现了左边数据源最后一个单元格带绿帽子,也就是文本格式。这就是导致2个公式求和结果不一样的原因。


在求和的时候,SUMIFS是直接忽略文本,这就导致了算少了。而SUMPRODUCT不管什么格式,都可以求和。正确的,应该是后者。

如何才能快速确认每个金额都是数字格式?

这个表格才30多行,一下子就找到了,而实际表格可能是几千行,用眼睛看肯定不靠谱。在隔壁列用ISNUMBER判断,数字格式的返回TRUE,文本格式的返回FALSE。


再将FALSE筛选出来就行。


当然,也可以不用刻意去找,直接选择C列,点分列,完成,就全部转换成数字格式,也就是全部都是TRUE。


在所有需要辅助函数,或者不规范的情况下,都是SUMPRODUCT占了绝对优势。这里,卢子再举几个案例说明。

1.按月份统计金额

用MONTH提取月份,每个参数都可以嵌套其他函数非常方便。
=SUMPRODUCT(( MONTH($A$2:$A$32)=D2)*$B$2:$B$32)


如果有跨年的,可以直接用TEXT提取年月再统计。如果月份是数字格式,记得加--转换格式。
=SUMPRODUCT(( --TEXT($A$2:$A$32,"emm")=D2)*$B$2:$B$32)


SUMIFS可不支持这种,需要用辅助列先提取月份才行。

2. 按账户计算余额

区域采用混合引用,这里下拉的时候就可以逐渐变大,求和区域可以用2个区域直接相减。SUMIFS可不支持这种,只能用2个SUMIFS相减才行。
=SUMPRODUCT((C$2:C2=C2)*(D$2:D2-E$2:E2))


3.按总成号ABC隔列求和工单号123对应的数据

不管一维还是二维,都是直接套上就行。而SUMIFS就不支持二维,除非特殊情况,比如ABC顺序跟原来一模一样。
=SUMPRODUCT(($A11=$A$3:$A$5)*(B$10=$B$2:$J$2)*$B$3:$J$5)

其实SUMPRODUCT挺好的,除了引用区域的时候不能引用整列,没啥缺点。

你可能想看:

有话要说...

取消
扫码支持 支付码