SUMIF vs SUMIFS performance

[gaj104]

Hi,

Has any one carried out any analysis on the performance of the 2 over a large dataset? I'm optimising a workbook and wondering which performs better, a SUMIF with a helper column, or SUMIFS direct to the columns?

Thanks

 

[Aladin Akyurek]

Hi,

Has any one carried out any analysis on the performance of the 2 over a large dataset? I'm optimising a workbook and wondering which performs better, a SUMIF with a helper column, or SUMIFS direct to the columns?

Thanks

The question is not very clear...

One would expect that:

=SUMIF(CondRange,Cond,SumRange)

=SUMIFS(SumRange,CondRange,Cond)

would share the same temporal profile.

If SUMIFS is faster than SUMIF at single condition, everybody on 2007 or later would like to know that.

 

[Jonmo1]

I think what is being asked is something like

=SUMIFS(SumRange,Cond1Range,Cond1,Cond2Range,Cond2)

is better than

ConcatRange(helper column): =Cond1&Cond2
=SUMIF(ConcatRange,Cond1&Cond2,SumRange)


Personally, I think the use of the helper column would be faster. But have not done any testing.
And the difference is likely very small.

 

[gaj104]

Yes, you are right on my not so clear clarity

I meant to say how the performance compares on a multiple criteria basis. In 2003 I would concatenate the 2 or more columns. In 2007 a straight sumifs on the columns is substituted, but wondering does this actually better performance?

Thanks

Edit: In fact jonmo explains it better than me Be interesting to know if anyone had done any actual testing.

 

[Scott Huish]

I would've used SUMPRODUCT prior to XL2007, so it's hard to say for me.

 

[Aladin Akyurek]

Yes, you are right on my not so clear clarity

I meant to say how the performance compares on a multiple criteria basis. In 2003 I would concatenate the 2 or more columns. In 2007 a straight sumifs on the columns is substituted, but wondering does this actually better performance?

Thanks

Edit: In fact jonmo explains it better than me Be interesting to know if anyone had done any actual testing.

Concatenation means additional operations. Trading off memory against speed might be less relevant here for Sumifs like SumIf operates on range objects and they are fast (faster than SumProduct, for example). Moreover, conditions which do not involve equality tests would be difficult to handle with concatenation.