Speed test: VBA vs. Formulae?

Hi all,

Short version - Is a VBA script liable to be quicker than multiple, heavy SumProduct formulae that take ~30 seconds to recalculate every time I ask them to look at different data?

Less short version - I currently have a behemoth of a spreadsheet, containing lots and lots of data. Sitting on top of this, I have a dashboard that is fed by a series of dynamic SumProduct formulae - the data which it selects being togglable by a series of drop-down menus. Ostensibly, it's quite nice and does its job very well. Unfortunately, it takes quite some time for the SumProduct formulae to recalculate whenever a new data set is selected.

A friend of mine suggested that I consider writing a VBA script to do the work instead. So, before I spend a couple of days actually going ahead and doing this, I wanted to get a general consensus on whether or not people actually think it'd be any faster.

Any ideas, folks?

Cheers,
S

SUMIF and SUMIFS (and COUNTIF/COUNTIFS) are much quicker than using SUMPRODUCT, particularly on large arrays. You can often concatenate ranges together and then just do one comparison with SUMIF to speed things up, depending on the complexity of your SP formulae.

Can you post a few examples?

Pete

A Pivot table approach would be an obvious solution

Answer: As the friend with the spreadsheet, I can now say this is solved. A combination of COUNTIFS and SUMIFS ended up taking the calculation time down from 2min+ to under 1sec.

Well, that's good to hear - well done.

One thing to consider is that those functions weren't available in Excel 2003 and earlier versions, so if you distribute the file to users with earlier versions then they will cause errors for them.

Pete