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