Difference between SUMIF(Condition, Values), SUMPROD(Condition, Values) and SUM(Condition*Values)

Question

Let's say I have an excel table with 2 columns: dates in cells A1 to A10 and values in B1 to B10.

I want to sum all the values of May dates. I have 3 possibilities:

{=SUM((MONTH(A1:A10)=6)*(B1:B10))}

or

=SUMPRODUCT((MONTH(A1:A10)=6)+0;B1:B10)

or

=SUMIFS(B1:B10;A1:A10;">="&DATE(2016;6;1);A1:A10;"<="&DATE(2016;6;30))

What is the best formula to use? In which case? And why?

I have found answers regarding the last two formulas, but nothing regarding the first one.

Answer 1

The first formula would give you an error if B1:B10 contains any text values, the second one won't (it will just ignore text in B1:B10). You can change the first one to allow text in B1:B10 by switching to this syntax:

=SUM(IF(MONTH(A1:A10)=6;B1:B10))

Both of the first two formulas will also give you an error if A1:A10 contains text - SUMIFS won't and can also handle error values in those ranges (as long as not in the sum range on a row that satisfies the conditions)

For those reasons SUMIFS is better, and faster as Scott says.

Disadvantages of SUMIFS:

Can't work with closed workbooks - is less flexible in that it can't accept arrays, so you can't use functions on the ranges

In your specific example SUMIFS only sums amounts for June 2016. The first two formulas will sum for any June date in any year, so that flexibility may suit you better in some circumstances

Answer 2

The first and Second (SUM and SUMPRODUCT) are array type formulas; they will iterate through the range, this is slow and if too many will cause a slow down in the calculation speed and even crash excel.

The third is not an array type formula and has been optimized, and as such can use full column references without detriment to speed.

When ever SUMIFS can be used it is recommended to use it.