Sumif / Sumproduct / Array

[thorpyuk]

Which formula is the most efficient? I need to put quite a lot of them into a spreadsheet :s

 

[DonkeyOte]

SUMIF if you don't have more than 1 criteria.

How many and what are the calulcations you intend to do ?

You may find there are efficient work arounds you can do creating Unique references (concatenated) which will allow you to use a SUMIF rather than having to use SUMPRODUCT.

 

[Jon von der Heyden]

Depends what you are doing... SUMPRODUCTS are handy when you have mulitple criteria summations / counts to perform. SUMIF / COUNTIF recalculate quicker than SUMPRODUCT in my experience. I only use array formula when I absolutely have to - they really do seem to slow my models down...

Perhaps someone can suggest which is the most effiocient formula to use if you describe what you are doing in a little more detail.

 

[xld]

As mentioned, if there is only one criteria, SUMIF/COUNTIF will beat SUMPRODUCT hands down for speed. These functions are optimised under the sheets to test a criteria over an array, SUMPRODUCT uses the array approach, testing them one at a time.

You can also simualte multiple conditions if they are simple using say COUNTIF. For instance, this SP formula

=SUMPRODUCT(--(A2:A200>=--"2008-01-01"),--(A2:A200<--"2008-02-01"))

can be achieved with

=COUNTIF(A2:A200,">=01/01/2008")-COUNTIF(A2:A200,">=01/02/2008")

and the latter is still quicker

 

[Aladin Akyurek]

Which formula is the most efficient? I need to put quite a lot of them into a spreadsheet :s

When there is just one condition to evaluate, a formula with SumIf is almost always more appropriate to invoke.

An array/matrix formula with If for conditional calculations is more general for it can be combined Sum, Max, Min, StDev, Median, Average, etc.

A SumProduct formula for (straight) multi-conditional counting and summing often slightly more efficient than an equivalent Sum/If formula, if set up appropriately.

 

[thorpyuk]

Thanks for all your responses