Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Which is faster sum(if) as an array or sumproduct?
I finally got my sum(if) based on more than one condition to work but it
takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? Thanks, Lee |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Which is faster sum(if) as an array or sumproduct?
It depends.
At this link: http://msdn2.microsoft.com/en-us/library/aa730921.aspx there are some VBA routines that will let you time the calculations. You'll find the code about 1/4 of the way down. Biff "Neophyte" wrote in message ... I finally got my sum(if) based on more than one condition to work but it takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? Thanks, Lee |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Which is faster sum(if) as an array or sumproduct?
In general, SUMPRODUCT is faster than SUM(IF()). How much faster depends
on the exact implementation, but anecdotally I've seen from less than 1% to as much as 50%. SUM(IF( bloats your file more, IIRC. There's some XL version dependence as well. See this old thread for some discussion: http://groups.google.com/group/micro...heet.functions /browse_thread/thread/1cb52ef08e1ade2d/ea1bc70584c4e587 It's hard to say without knowing more about your layout, but you may see dramatic gains if you can use helper columns/rows to do partial array calculations so that the array formulae don't recalculate as much or as often. For instance, with =SUMPRODUCT(--(A1:A1000=1),--(B1:B1000=2),--(C1:C1000=3),D1:D1000) If columns A and B don't change very often, but column C does, using an extra column with (array-entered): E1:E1000: =(A1:A1000=1)*(B1:B1000=2)*D1:D1000 Then a final column: F1:F1000 =SUMPRODUCT(--(C1:C1000=3),E1:E1000) Will require only two arrays be evaluated instead of four when a value in column C is changed. In article , "Neophyte" wrote: I finally got my sum(if) based on more than one condition to work but it takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Which is faster sum(if) as an array or sumproduct?
For what it's worth I just built a spreadsheet with 70,000 dependencies, all
of which are SUMPRODUCT functions summarizing a large table of data in various ways. It recalculates instantly, and this is one a machine running XL 2000 with 256mb of RAM. So it seems a safe bet that with your configuration SUMPRODUCT would also be quick. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Neophyte" wrote: I finally got my sum(if) based on more than one condition to work but it takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to use the free time to do something else? Thanks, Lee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which is faster? SUMPRODUCT or VLOOKUP, or another alternative? | Excel Worksheet Functions | |||
SUMPRODUCT as array formula? | Excel Worksheet Functions | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
VLOOKUP in SUMPRODUCT array | Excel Worksheet Functions | |||
Another SUMPRODUCT array anomaly | Excel Worksheet Functions |