Faster alternatives to array formulas

**Spellbound** · 01-10-2012, 12:47 PM

Hi all

Just going through the process of trying to improve and speed up my main project workbook and would appreciate any comments or advice on faster alternatives to array formulas.

In one worksheet I have 1500 rows of which 5 columns utilise array formulas as shown below. By the end of the year, this will double as the workbook increases in size and content.

Please Login or Register  to view this content.

All names are set up as Dynamic ranges.

I could use SUMPRODUCT to achieve the same result as in:

Please Login or Register  to view this content.

or even a UDF which I have found very useful as in:

Please Login or Register  to view this content.

Ideally, it would be nice to make up new workbooks with each of the alternatives in place to time them but due to the layout of the spreadsheet, replacement of all the array formulas is going to take some time and I would appreciate any comments or even other possible solutions before making the big change.

Thanks ...David

**NBVC** · 01-10-2012, 12:51 PM

Have you tried Pivot tables instead?

Also, if you move up to 2007 or 2010 versions, you can use much more efficient SUMIFS function.

**Spellbound** · 01-10-2012, 12:59 PM

Hi NBVC

I use 2 Pivot Tables in other parts of this workbook but I do not feel they would be suited to this particular data set.

I did try going over to 2007 once but it trashed this workbook so badly that it put me off, not to mention the time needed to be spent on a new learning curve; however from my research I take your point about the SUMIFS function, which could really be handy in some situations.

David

**NBVC** · 01-10-2012, 01:08 PM

You can also use helper columns that return the value from Issues_Term when the other criteria is matched on a row per row basis... then simply sum that column.... that too is more efficient, but requires the extra column(s).

**Spellbound** · 01-10-2012, 08:37 PM

I'm not fond of helper columns but it is something else that I have considered but not put into practice and it would mean adding a minimum of 5-7 helper columns.

I was reading about using a combination of Sumproduct in conjunction with Index/Match which may be another alternative that I'm going to try out this week, if it works.

Will post an update if it works.