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.

barry houdini barry houdini · Accepted Answer · 2017-12-11T18:40:42

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

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

2 Answers