Sumproduct Slow

[wx4usa]

I have 13 columns of data with 30000 rows. Most data is numeric some is
2 words at the most.

The computer calculates cells very slowly and sometimes will not save
due to insufficient resources. I have 1gig ram.

An example formula might be.

=sumproduct((month=a1)*(year=a2)*(salesperson=a3)*(sales)

I may have as many as 120 of these formulas on a sheet referencing 12
different salespeople and 10 other key perfromance measures.

Is there anything I should look for or change to speed it up?
What about save and calculation options on the tools/options tab. I
dont know about these.
Would it be faster if my dbase and my worksheets are in separate books?
or does sumproduct "LOOK" anyway?

Any help would be greatly appreciated.

 

[Roger Govier]

Hi

I would take a look at using a Pivot Table instead of these Sumproduct
formulae.
In my experience, Pivot Tables are faster.

For more information on Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx


also, to look at ways of speeding things up in general, go to Charles
Williams site
http://www.decisionmodels.com/optspeedb.htm

 

[Bob Phillips]

Consider doing some of the repeated calculations in helper cells and
reference those.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[wx4usa]

Thanks for teh reply Roger,

Can I use pivit tables to pull my data from the database and then
within the table perform other calculations?

Example, I pulll salesperson, number of customers from the dbase to
the pivot table, then average revenue per customer from that data?

 

[Roger Govier]

Yes you can do all sorts of calculations in the PT itself, Sum, Count,
Average, Max, Min etc. as well as set up calculated fields and
calculated items.

You can also extract data from the PT to perform further calculations
using the GetPivotData function.