Ideas for an alternate (faster) approach to sumproduct?

[ker_01]

I have a colleague's workbook (XL07) that had about 15K sumproduct formulas,
each referencing a sheet of raw data that has about 80K rows (and grows by
20K rows per month, so it will only get slower).

I removed the sumproduct formulas, thinking that untimely recalculations
might be part of what slows down the overall workbook. I replaced it with
application.evaluate(sumproduct(my conditions)) then pasted the resulting
value into the target cells. This eliminates any possible recalculation of
those cells outside of the macro.

Unfortunately, running that macro is slower than molasses, so I'm looking
for a better approach. I *do* turn off calculation and screenupdating at the
top of the macro. My next step is to load the source data into two arrays and
do my comparisons there (it should save me some time), but I'm not sure it
will be as fast as I'd like. I could pre-sort the raw data so that all the
target data for each sumproduct is co-located, but I'd still have to figure
out the range of rows to feed into the sumproduct - maybe use a match
statement to figure out where to start, but how to figure out the last row to
use for that data set?

I appreciate any ideas you might have!
Thank you,
Keith

 

[Dave Peterson]

I'd do some experiments with pivottables.

In fact, I'd do some basic experiments with helper columns and plain (non-array
entered) formulas.