SUMPRODUCT vs SUMIFS

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

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.

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.