SUMPRODUCT 与 SUMIFS [英] SUMPRODUCT vs SUMIFS
问题描述
我正在尝试制作 Excel 的公司内部指南(我们是一家小型非营利组织，非常需要某种基线指南).但是，我一直在试图解释 SUMPRODUCT 和 SUMIFS 之间的差异.
I'm trying to make an in-company guide to Excel (we're a small non-profit and sorely need some sort of baseline guide). However, I've gotten stuck trying to explain the differences between SUMPRODUCT and SUMIFS.
我的理解是 SUMPRODUCT 在 Excel 2007 之前被用作具有多个 SUMIF 条件(除其他外)的一种方式.既然有了SUMIFS，那么两个公式的容量有没有区别呢?
My understanding is that SUMPRODUCT was used before Excel 2007 as a way to have multiple SUMIF criteria (among other things). Now that SUMIFS is available, is there any difference in the capacity of both formulas?
我环顾四周，发现 SUMIFS 往往更快，但仅此而已......感谢任何见解和/或阅读材料！
I've looked around a bit and found that SUMIFS tends to be faster, but that was it... Any insights and/or reading material is appreciated!
推荐答案
SUMPRODUCT 可以比 SUMIFS 更灵活地使用，因为你可以用 SUMIFS 中的其他函数修改范围代码>SUMPRODUCT，例如如果您在 A2:A100 中有一个日期范围，您如何总结 B2:B100 中 December 日期(在任何年份)中的相应金额)?
SUMPRODUCT can be used more flexibly than SUMIFS because you can modify the ranges with other functions in SUMPRODUCT, e.g. if you have a range of dates in A2:A100 how can you sum the corresponding amounts in B2:B100 for December dates (in any year)?
你可以用这个公式得到答案
You can use this formula to get the answer
=SUMPRODUCT((MONTH(A2:A100)=12)+0,B2:B100)
你不能用SUMIFS轻松做到这一点，尽管如果日期都在一年内，你可以只使用范围的起点和终点作为SUMIFS，例如仅适用于 2014 年 12 月:
You can't do that easily with SUMIFS, although if the dates are all in one year you can just use the start and end points of the range as the criterion in SUMIFS, e.g. for December 2014 only:
=SUMIFS(B2:B100,A2:A100,">="&DATE(2014,12,1),A2:A100,"<"&DATE(2015,1,1)))
SUMPRODUCT 也可用于引用封闭工作簿中的数据，SUMIFS 不能这样做 - 请参阅此处
SUMPRODUCT can also be used to reference data in closed workbooks, SUMIFS can't do that - see here
http://support.microsoft.com/kb/260415
...但总的来说 SUMIFS 明显更快，我看到引用了 5x 数字，但我还没有验证.
...but in general SUMIFS is significantly quicker, I've seen a 5x figure quoted but I haven't verified that.
有关 SUMPRODUCT 的多种有趣用途，请参阅 MS Excel MVP Bob Philips
For multiple interesting uses of SUMPRODUCT see this article by MS Excel MVP Bob Philips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
这篇关于SUMPRODUCT 与 SUMIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
