SUMPRODUCT vs 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.

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

回答1:

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

回答2:

SUMPRODUCT works like array formulas. That gives you a lot more flexibility than SUMIF. Here's an example where you can have an OR (+ in summproduct)

=SUMPRODUCT((A1:A10="Marketing")*((B1:B10="North")+(B1:B10="South"))*(C1:C10))

That will sum everything that has marketing in A and either North or South in B.

Here's an example that uses a function on a range

=SUMPRODUCT((YEAR(A3:A7)=2014)*(B3:B7=1)*(C3:C7))

That will sum everything where the year of the date in col A is 2014 and col B is 1.

回答3:

SUMPRODUCT can actually sum the products from multiple arrays, SUMIFS will sum only values from a single range.

e.g. for input data

10    1
20    2
30    3

=SUMPRODUCT(A1:A3, B1:B3) => 10*1 + 20*2 + 30*3 = 140

回答4:

1. SUMIFS typically works within 30% of a similar SUMPRODUCT formula's calculation load.
2. SUMIFS can use full column references (e.g. A:A instead of A2:A999) without compromising calculation lag the way SUMPRODUCT does by only actually calculating the used range.
3. SUMIFS happily skips over text values in a column that would create an error with SUMPRODUCT in a mathematical operation.
4. While SUMPRODUCT does provide some functionality that SUMIFS misses (the OR functionality mentioned above being one of the most commonly required), the newer SUMIFS is by far the preferred method whenever it can be used.