SUMIFS V's SUMPRODUCT - Calc Time

[shemayisroel]

Hi,


I'm working on a bit of a beast and need to be as calculative effecient as possible.

Part o fthe solution would be to do certain counts and sum's based on multiple criteria - something that could easily be handled by SUMIFS or SUMPRODUCT.

My question is, in terms of calculation speed/effeciency which would generally be the better of the 2. Happy to also take on personal experience/feedback.

 

[Peter_SSs]

Sumifs

 

[T. Valko]

Hi,


I'm working on a bit of a beast and need to be as calculative effecient as possible.

Part o fthe solution would be to do certain counts and sum's based on multiple criteria - something that could easily be handled by SUMIFS or SUMPRODUCT.

My question is, in terms of calculation speed/effeciency which would generally be the better of the 2. Happy to also take on personal experience/feedback.

In Excel 2007/2010 the new SUMIFS/COUNTIFS functions are more efficient than the equivalent SUMPRODUCT formulas.

As a quick test I used up to 1000 rows of data and did a 2 condition sum.

These were the formulas tested:

=SUMIFS(C1:Cn,A1:An,">=50",B1:Bn,">=50")
=SUMPRODUCT(--(A1:An>=50),--(B1:Bn>=50),C1:Cn)

These are the results of the tests. I did 5 calculations on each formula for each different range size. The results are the average calculation times in seconds for 5 calculations.

Book1
	A	B	C
1	# of rows	SUMIFS	SUMPRODUCT
2	100	0.001528	0.001686
3	500	0.001782	0.002484
4	1000	0.002052	0.003514
Sheet1

The results will vary by machine but they can be used to clearly demonstrate the relative difference in efficiency between the tested formulas.

You can do your own testing. There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

There is also the commercial product FastExcel which can be found here:

http://www.decisionmodels.com/

 

[shemayisroel]

Gents - thank you very much!

 

[brettdj]

We went through a recent similar exercise for a large summation process and the SUMIFS were 5 times faster (using FastExcel)

BUT SUMPRODUCT does work on a closed workbook whereas SUMIFS doesn't - something that may be relevant to your choice

Cheers

Dave

 

[T. Valko]

Gents - thank you very much!

You're welcome!