14

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!

feetwet
  • 3,248
  • 7
  • 46
  • 84
Kelley Hamrick
  • 197
  • 1
  • 2
  • 13
  • 2
    Part of the answer is here (see the 2 answers): http://stackoverflow.com/questions/11480017/sumproduct-sumif-how-does-this-work?rq=1 – dan Dec 17 '14 at 22:02
  • Excel 2010 has a [bug](https://social.technet.microsoft.com/Forums/office/en-US/cda9fef8-8ad4-4d56-8939-49b1ae50c4e7/bug-in-excel-2010-sumif-sumifs-functions-edit-also-averageif-ifs?forum=excel) for SUMIFS() formula. – Mehper C. Palavuzlar Apr 15 '16 at 10:48
  • One advantage of SUMIFS is that it can handle text or an error by simply skiping these cells. But, while using SUMPRODUCT you will get an error message. (Which can also be an advantage sometimes :-)) – MrZH6 Jun 21 '19 at 11:56

4 Answers4

12

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Barry, I've validated SUMIFS being 3X faster using Charles William's FastExcel. Significant for big models. – brettdj Dec 19 '14 at 03:02
  • `SUMIFS` over 5x faster than `SUMPRODUCT` per test here: https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/ – feetwet May 14 '20 at 22:04
6
  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.
5

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

Aprillion
  • 21,510
  • 5
  • 55
  • 89
5

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.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • The particular circumstance you address in your first formula may also be derived with `=SUMPRODUCT((A1:A10="Marketing")*(B1:B10={"North","South"})*(C1:C10))` or `=SUMPRODUCT(SUMIFS(C:C,A:A,"marketing",B:B, {"north","south"}))`. Half of this and half of that; same difference. –  Dec 18 '14 at 13:45
  • That's nice. Yours is a little easier to read, I think, rather than parsing the parentheses. – Dick Kusleika Dec 18 '14 at 19:50