Which is Better / More Functional / Accurate

[Rob]

I was wondering if I can get an opinion on which is better; SUMIF OR
SUMPRODUCT. I suspect the answer will be SuMPRODUCT, however there are so
many ways of using this function that I can't get a grasp of how to use it in
what situation. So IF the resounding answer is indeed SUMPRODUCT - Can
someone point me to a place/resource where I can finally fully learn and
understand that function? Please?

Here are two examples of the same exact result that got me to wonder about
this...

SUMIF:
=IF(SUMIF(Sheet3!$B$3:$B$803,Sheet1!$C19,Sheet3!$F$3:$F$803)>0,SUMIF(Sheet3!$B$3:$B$803,Sheet1!$C19,Sheet3!$F$3:$F$803),"-")

SUMPRODUCT:
=IF(SUMPRODUCT(--(Sheet3!$B$3:$B$803=Sheet1!$C19),Sheet3!$F$3:$F$803)=0,"-",SUMPRODUCT(--(Sheet3!$B$3:$B$803=Sheet1!$C19),Sheet3!$F$3:$F$803))


Thank You Very Much in Advance!
Rob

 

[Luke M]

In your case, I'd use SUMIF. It tends to be faster as XL doesn't have to
store as many array values, and it involves fewer calculation steps.

The advantage of SUMPRODUCT is that you could have more than one criteria
being checked (say, add up all values that correspond to "A" or "B").

Link to a longer explanation regarding the different methods/speeds.
http://www.ozgrid.com/Excel/sum-if.htm

 

[T. Valko]

As a general rule, when you're dealing with a single condition use
SUMIF/COUNTIF. When you're dealing with more than one condition use
SUMPRODUCT.

However, there are times when you can use SUMIF/COUNTIF for multiple
conditions.

See this for more info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Also of note, if you're using Excel 2007 it comes with 2 new functions that
are a combination of SUMPRODUCT and SUMIF/COUNTIF. Those new functions are
called SUMIFS and COUNTIFS. They give you the efficiency of SUMIF/COUNTIF
combined with *some* of the functionality of SUMPRODUCT all rolled into a
single function.