Optimize SumProduct

[Guest]

Hi,

I have a sheet with many sumproducts and it takes forever to
recalculate.

Here is the basic idea, I want to calculate the number of KGS and
Dollars for each company in my list. There are dozens of companies and
the bank of sales they are looking at has 5000 rows.

both of the calculations are essentially the same except for the final
column to sum, is there a way to check the conditions I have once, then
use that calculation for both the final sums?

here is an example of the formula for KGS:
=SUMPRODUCT(('Total Pole Sales'!$C$2:$C$5412=$A$1)*('Total Pole
Sales'!$J$2:$J$5412=2004)*('Total Pole Sales'!$E$2:$E$5412='Summary
Table'!C2)*('Total Pole Sales'!G$2:G$5412))

FOR REV: =SUMPRODUCT(('Total Pole Sales'!$C$2:$C$5412=$A$1)*('Total
Pole Sales'!$J$2:$J$5412=2004)*('Total Pole
Sales'!$E$2:$E$5412='Summary Table'!C2)*('Total Pole
Sales'!H$2:H$5412))

As you can see, they are the same except for the final argument.
Any suggestions?

thanks!

 

[Frank Kabel]

Hi
the following would be slightly faster:
=SUMPRODUCT(--('Total Pole Sales'!$C$2:$C$5412=$A$1),--('Total Pole
Sales'!$J$2:$J$5412=2004),--('Total Pole Sales'!$E$2:$E$5412='Summary
Table'!C2),--('Total Pole Sales'!G$2:G$5412))

But in your case I would consider using a pivot table instead:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2004/11/26/creating-a-simple-pivot-t
able/trackback/

 

[Tushar Mehta]

In addition to Frank's suggestion about a PivotTable, you might also
want to look at
Create a data subset for further analysis
http://www.tushar-mehta.com/excel/tips/limited_subset.pdf

It is a draft of one segment of a much longer document. I expedited
its creation in response to your post. This segment deals with the
creation and use of parameterized queries.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Chris,
I had a similar problem - Large database, many SP formulas, took over 30
minutes to recalculate. I'm now uising SUMIF with concatination The same
workbook recalculates in less than 5 minutes. Here's how:

For example, your original database has fields: A, B, code, quantity
Add a helper concatinated field AB (=RC[-2]&RC[-1]).
Apply a range name to each column field (use the field headers as names)
Now you're ready..
How many blues were sold in 2004? ... =SUMIF(AB,"=2004")
You might only want to sum items having code 2. You can sort the data so
that the code 2s appear at the top of the list, then re-set the range names
so that only the code 2s are included, then recalculate. Using a mix of these
techniques you can process your large databases in a fraction of the time
taken by SUMPRODUCT methods

A B AB code Quantity
Blue 2004 Blue2004 1 530,123
Red 2003 Red2003 1 23,456
Blue 2004 Blue2004 2 12,894

HTH