I use some sum if array formulas (i.e. {=SUM(IF(ResourceDataProjectNumber=$B4,IF($A4=ResourceDataManager,ResourceDataJanuary)))} - These formulas use dynamic ranges - Thanks OzGrid for introducing me to these gems of excel.
I need to use these formulas as I am contrasting time that we are forecasting for individuals to actual time booked by these individuals. As this time originates from from two totally different data sources (I cannot combine them), I have no idea how to display this in a pivottable.
Since there is so much data, and so many formulas (6474 array formulas). These formulas are searching through a tremendous amount of data (41000 cells from one data source - and 23,000 cells in another). The sheet is extremely slow to calculate (Roughly 2.5 minutes - I am on a 1.8 ghz intel machine (not sure what type of processor) with 512 megs of ram).
Any suggestions as to how to speed this up?
Slow File - Using Sum If Array Formulas
-
-
Re: Slow File - Using Sum If Array Formulas - Any Suggestions Appreciated
Have you thought about or can you indeed use a database such as Access ?
Array formulas are notoriously memory hungry.
-
Re: Slow File - Using Sum If Array Formulas - Any Suggestions Appreciated
Quote from eduboysWhat about concatenating several cells, and using that in a sumif() formula instead. Will that make it any faster?
I believe it should make it faster. Also, if the range ResourceDataProjectNumber is sorted, it could probably be made even faster using another solution without the need to concatenate columns. Is the range sorted?
-
Re: Slow File - Using Sum If Array Formulas - Any Suggestions Appreciated
eduboys, please do NOT use wordss like "Any suggestion appreciated" in Thread titles. All this does is water down the forums search ability and makes finding answers a LOT harder. Think of you fellow members when contructing Thread Titles please.
-
Re: Slow File - Using Sum If Array Formulas - Any Suggestions Appreciated
I would strongly advise against array formulas with this much data. You will end up in a deep dark hole! Use [pt]*[/pt] and/or the Database Functions (see Excel help).
Also, see: Efficient Excel Spreadsheet Designs
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!