Conditional summing with large amounts of data

Hi

I have a workbook contains 3 sheets

"2002": The first is data for 2002. There are about 35,000 rows of data.
Each row has 5 identifying features (ie columns) plus two of actual numbers.

"1995": The second sheet is similar data for 1995. However there are only
about 8,000 records and only 4 identifying factors.

(The identifying factors are pre-defined lists, with between 8 and 33
options available.)

The third sheet is a summary sheet. What I want to do is dynamically return
a 2D table that summarises any combination of that data (including
aggregating and filtering). I have successfully implemented this using the
following formula:

=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D18)*
(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*
IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B$4&"35393")=$F$4))*
IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B$5&"35393")=$F$5))*
(INDIRECT($D$6&"!J2:J35393")))

D6 contains either 2002 or 1995 (ie the sheet names).
B2:B5 contains the letters of the columns which are being filtered and/or
displayed (dynamically retrieved by simple formulae)
F4:F5 contain possible filters

This formula is copied across a 33 by 33 range (large enough to cover all
possible combinations of rows/columns)

Unfortunately this calculates very slowly - it currently takes about
30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to
recalculate a lot as you need to change other options to set up the table how
you want it.

I have read a few sites on optimising speed of calculations etc, and they
tend to suggest using alternatives to the sum() function but I am not sure
how they apply here given the versatility I am hoping for.

Any advice or suggestions would be very much apprecaited

Regards

Revontulet

Bump

"Revontulet" wrote:

> Hi
>
> I have a workbook contains 3 sheets
>
> "2002": The first is data for 2002. There are about 35,000 rows of data.
> Each row has 5 identifying features (ie columns) plus two of actual numbers.
>
> "1995": The second sheet is similar data for 1995. However there are only
> about 8,000 records and only 4 identifying factors.
>
> (The identifying factors are pre-defined lists, with between 8 and 33
> options available.)
>
> The third sheet is a summary sheet. What I want to do is dynamically return
> a 2D table that summarises any combination of that data (including
> aggregating and filtering). I have successfully implemented this using the
> following formula:
>
> =SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D18)*
> (INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*
> IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B$4&"35393")=$F$4))*
> IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B$5&"35393")=$F$5))*
> (INDIRECT($D$6&"!J2:J35393")))
>
> D6 contains either 2002 or 1995 (ie the sheet names).
> B2:B5 contains the letters of the columns which are being filtered and/or
> displayed (dynamically retrieved by simple formulae)
> F4:F5 contain possible filters
>
> This formula is copied across a 33 by 33 range (large enough to cover all
> possible combinations of rows/columns)
>
> Unfortunately this calculates very slowly - it currently takes about
> 30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to
> recalculate a lot as you need to change other options to set up the table how
> you want it.
>
> I have read a few sites on optimising speed of calculations etc, and they
> tend to suggest using alternatives to the sum() function but I am not sure
> how they apply here given the versatility I am hoping for.
>
> Any advice or suggestions would be very much apprecaited
>
> Regards
>
> Revontulet