SUMPRODUCT对SUMIFS [英] SUMPRODUCT vs SUMIFS

问题描述

我正在为Excel制作一份公司指南（我们是一个小的非营利组织，并且非常需要某种基准线）。但是，我试图解释SUMPRODUCT和SUMIFS之间的差异。

我的理解是，在Excel 2007之前使用SUMPRODUCT作为具有多个sumif标准的方法（除其他外）。现在SUMIFS可用了，两种公式的容量有差异？

我已经看了一下，发现SUMIFS往往会更快，但那就是...任何见解和/或阅读材料都是赞赏！ p>

感谢

解决方案

SUMPRODUCT 可以比 SUMIFS 更灵活地使用，因为您可以使用 SUMPRODUCT 中的其他功能修改范围，例如如果您在 A2中有一系列日期：A100 如何将 B2中的相应金额相加：B100 code> 12月日期（任何一年）？

您可以使用此公式获得答案

= SUMPRODUCT（（MONTH（A2：A100）= 12）+ 0，B2：B100）

您不能轻易地使用 SUMIFS ，尽管如果日期全部在一年内，您可以使用开始和结束点范围作为 SUMIFS 中的标准，例如仅2014年12月：

= SUMIFS（B2：B100，A2：A100，> =& DATE（2014,12， 1），A2：A100，&DATE（2015,1,1））

SUMPRODUCT 也可用于引用关闭工作簿中的数据， SUMIFS 不能这样做 - 见这里

http://support.microsoft.com/kb/ 260415

...但一般来说， SUMIFS 明显更快，我看到一个 5x 引用数字，但我还没有验证。

对于 SUMPRODUCT的多个有趣用途通过MS Excel MVP看到这篇文章 Bob Philips

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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.

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?

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!

Thanks

解决方案

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)

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 can also be used to reference data in closed workbooks, SUMIFS can't do that - see here

http://support.microsoft.com/kb/260415

...but in general SUMIFS is significantly quicker, I've seen a 5x figure quoted but I haven't verified that.

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屋！