Anyone know of a faster calculation for the CPU???

[mdavidge]

Hello,

I have a macro that pastes formulas. The macro described below takes 1 hour and 20 minutes to run. I am wondering if anyone knows of a better way to perform these calculations in Excel. The calculation appears to tax the CPU and the this is the time killer.

The spead sheet is rather large with roughly 65,000 rows and 170 columns. I have 140 columns with the SUMPRODUCT formula listed below in the second row. My macro selects an adjasent area with 920 rows and pastes 140 colums with SUMPRODUCT (formulas like below) down with the SUMPRODUCT formulas. The lookup data is what makes up the 65,000 rows. This calculation of 920 rows X 140 colums takes the CPU 1 hour and 20 minutes to complete.

=SUMPRODUCT(--($A$4:$A$65000=$Y4),--($L$4:$L$65000="SEALS"),($M$4:$M$65000))

I have build the calculation in Access only to find that Excel cannot transfer more than 50 Fields from an Access Query back to Excel in an automated way. I have built both ADO code and tried Import External Data. Both will return the info if pulled from a Access Table, but not from a Query.

Any help is appreciated.

Mike

 

[mdavidge]

Aladin,

I have 3 colums of product descriptions. One is the Product, second is a sub group, and the third is a product family - Minor, Mid and Major if you will. The "Seals" is a major group. Another calculation will look for "Voyager Seals" in a minor group column.

I hope this explains it.

Mike

 

[mdavidge]

Norie,

I appreciate the code. My code is cumbersome because it is looking for the bottom of row of a different section of the worksheet with the same number of rows that this calculation will have.

I did learn something new from your solution though.

Thanks,
Mike

 

[tusharm]

Explore alternatives that leverage existing native power rather than the brute force use of SUMPRODUCT (--

Option 1: Treat the XL data set as the source for SQL queries that provide you with the conditional totals you need.

Option 2: Consider a native function like DSUM

Option 3: Use Autofilter to restrict the number of rows for analysis. Then, use SubTotal.

Hello,

I have a macro that pastes formulas. The macro described below takes 1 hour and 20 minutes to run. I am wondering if anyone knows of a better way to perform these calculations in Excel. The calculation appears to tax the CPU and the this is the time killer.

The spead sheet is rather large with roughly 65,000 rows and 170 columns. I have 140 columns with the SUMPRODUCT formula listed below in the second row. My macro selects an adjasent area with 920 rows and pastes 140 colums with SUMPRODUCT (formulas like below) down with the SUMPRODUCT formulas. The lookup data is what makes up the 65,000 rows. This calculation of 920 rows X 140 colums takes the CPU 1 hour and 20 minutes to complete.

=SUMPRODUCT(--($A$4:$A$65000=$Y4),--($L$4:$L$65000="SEALS"),($M$4:$M$65000))

I have build the calculation in Access only to find that Excel cannot transfer more than 50 Fields from an Access Query back to Excel in an automated way. I have built both ADO code and tried Import External Data. Both will return the info if pulled from a Access Table, but not from a Query.

Any help is appreciated.

Mike