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

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I'm aware that there are two different methods

Code:
=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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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).
 
Upvote 0
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>
 
Upvote 0
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.

arraysample.jpg
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top