Sum Product vs. Array ... which is faster?

[jmckeone]

I'm aware that there are two different methods

=sumproduct(--(condition 1)*(condition 2),SUMRANGE)

{=SUM(IF(((CONDITION 1)*(CONDITION 2)),TRUE,FALSE))}

I often execute such formulas on big spreadsheets and wondered if there is any benefit to using one over the other.

 

[millhouse123]

if you have Excel 2007 you can just use the new sumifs function (note the arguments in different order than the sumif function). If you only have 2003 then I prefer the sumproduct but have never really noticed a difference in calc. time.

 

[xld]

SUMPRODUCT will be slightly faster, but they are both processing arrays, so compared to functions that have array processing at a low-level, like SUM and SUMIF, they are both much slower (although of course they do things the others cannot).

 

[theozz]

Hi ! Here Suggest to you

<table style="border-collapse: collapse;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks.</td></tr><tr><td width="713"><table colspan="2" rowspan="8" bgcolor="#939393" border="0" cellspacing="1" width="713"><col width="83"><col width="630"><tbody bgcolor="#ffffff"><tr height="18"><td align="left" bgcolor="#ffffff">formula 1</td><td align="left" bgcolor="#ffffff"> =sumproduct(--(condition 1)*(condition 2),SUMRANGE)</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">formula 2</td><td align="left" bgcolor="#ffffff"> {=SUM(IF(((CONDITION 1)*(CONDITION 2)),TRUE,FALSE))}</td></tr><tr height="18"><td align="left" bgcolor="#ffffff"></td><td align="left" bgcolor="#ffffff"></td></tr><tr height="18"><td colspan="2" align="left" bgcolor="#ffffff">I think 2 formula almost Similar</td></tr><tr height="18"><td colspan="2" align="left" bgcolor="#ffffff">but...</td></tr><tr height="18"><td colspan="2" align="left" bgcolor="#ffffff">formula 1 is few faster</td></tr><tr height="18"><td colspan="2" align="left" bgcolor="#ffffff">why?</td></tr><tr height="18"><td colspan="2" align="left" bgcolor="#ffffff">a) by sumproduct (condition1,condition2…) share by "," then assembley "Multiplicate"</td></tr></tbody></table><table colspan="2" rowspan="8" bgcolor="#939393" border="0" cellspacing="1" width="713"><col width="83"><col width="630"><tbody bgcolor="#ffffff"><tr height="18"><td colspan="2" align="left" bgcolor="#ffffff">b) user put "*" program convert "*" to assemble and do "Multiplicate"</td></tr><tr height="18"><td align="left" bgcolor="#ffffff"></td><td align="left" bgcolor="#ffffff">so</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">formula 1</td><td align="left" bgcolor="#ffffff">convert = 1 time, assemble Multiplicate1 time and auto summary</td></tr><tr height="18"><td rowspan="3" align="left" bgcolor="#ffffff">formula 2</td><td align="left" bgcolor="#ffffff">convert = 1 time, assemble Multiplicate1 time</td></tr><tr height="18"><td align="left" bgcolor="#ffffff">and translate True False to Number and Summary Procedures are needed</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">more need translate / Summary Procedures are needed</td></tr><tr height="18"><td align="left" bgcolor="#ffffff"></td><td align="left" bgcolor="#ffffff"></td></tr><tr height="18"><td align="left" bgcolor="#ffffff"></td><td align="left" bgcolor="#ffffff">I suggest .. use sumif with sevral conditional column</td></tr></tbody></table></td></tr><tr><td colspan="2"> <table style="border-collapse: collapse;"><tbody bgcolor="#ffffff"> <tr height="22"><td> My test... formula 1 is faster than formula 2 (5~30%) </td></tr></tbody></table></td></tr></tbody></table>

 

[jmckeone]

To give a bit more insight into what I'm doing will perhaps help to suggest if my method is best for the application.

I have a page with 52 weeks of dates on the left and anywhere from 10 to 50 columns representing distinct locations. The pool of data is evaluated for the date column and for a code column where each location may have up to 4 different codes. Below is a truncated example of the final data layout. If there is a better approach I'm all ears.