Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Optimize SumProduct
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! |
#2
|
|||
|
|||
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/2...simple-pivot-t able/trackback/ -- Regards Frank Kabel Frankfurt, Germany "chris" schrieb im Newsbeitrag ... 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! |
#4
|
|||
|
|||
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 "chris" wrote: Hi, I have a sheet with many sumproducts and it takes forever to recalculate. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Optimize SumProduct | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions | |||
Sumproduct in Excel Spreadsheet to read Access db table | Excel Worksheet Functions |