| 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 Frank
i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
|
#3
|
|||
|
|||
|
Hi
see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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
|
|||
|
|||
|
Hi Frank
thanks for this, i can't get that group using msnews.microsoft.com :( Cheers JulieD "Frank Kabel" wrote in message ... Hi Excel.worksheetfunctions But the solution was not really a solution: - you could gain some performance improvement by using the '--' notation (approx. 5%) - I'd better use a pivot table for this -- Regards Frank Kabel Frankfurt, Germany "JulieD" schrieb im Newsbeitrag ... Hi Frank i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
|
#5
|
|||
|
|||
|
Hi
Excel.worksheetfunctions But the solution was not really a solution: - you could gain some performance improvement by using the '--' notation (approx. 5%) - I'd better use a pivot table for this -- Regards Frank Kabel Frankfurt, Germany "JulieD" schrieb im Newsbeitrag ... Hi Frank i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
|
#7
|
|||
|
|||
|
Hi Tushar
i subscribe to that group but can't see Christopher's post there so i thought that there must be another group (although i know i don't see all posts for some reason) Cheers JulieD "Tushar Mehta" wrote in message ... The newsgroup is microsoft.public.excel.worksheet.functions -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Frank thanks for this, i can't get that group using msnews.microsoft.com :( Cheers JulieD "Frank Kabel" wrote in message ... Hi Excel.worksheetfunctions But the solution was not really a solution: - you could gain some performance improvement by using the '--' notation (approx. 5%) - I'd better use a pivot table for this -- Regards Frank Kabel Frankfurt, Germany "JulieD" schrieb im Newsbeitrag ... Hi Frank i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
|
#8
|
|||
|
|||
|
It's strange you don't see all the posts. Do you have any filters that
might be affecting the displayed posts? In any case and for what it's worth, the message id of the worksheet.functions post is -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar i subscribe to that group but can't see Christopher's post there so i thought that there must be another group (although i know i don't see all posts for some reason) Cheers JulieD "Tushar Mehta" wrote in message ... The newsgroup is microsoft.public.excel.worksheet.functions -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Frank thanks for this, i can't get that group using msnews.microsoft.com :( Cheers JulieD "Frank Kabel" wrote in message ... Hi Excel.worksheetfunctions But the solution was not really a solution: - you could gain some performance improvement by using the '--' notation (approx. 5%) - I'd better use a pivot table for this -- Regards Frank Kabel Frankfurt, Germany "JulieD" schrieb im Newsbeitrag ... Hi Frank i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
|
#9
|
|||
|
|||
|
Hi Tushar
no idea why i don't see all posts - microsoft.public.excel groups aren't tooo bad ... however, there's lots of times that i only see the first reply to a post rather than the posts itself - in microsoft.public.project this happens about 95% of the time. The only "solution" that i've come across is to unsubscribe and then re-subscribe on a regular basis but then i lose all my "watched" threads :( i'm using outlook express an no, no filters / rules or anything else that i can think of. if you have any ideas for fixing this i'ld be very interested Cheers julieD "Tushar Mehta" wrote in message ... It's strange you don't see all the posts. Do you have any filters that might be affecting the displayed posts? In any case and for what it's worth, the message id of the worksheet.functions post is -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar i subscribe to that group but can't see Christopher's post there so i thought that there must be another group (although i know i don't see all posts for some reason) Cheers JulieD "Tushar Mehta" wrote in message ... The newsgroup is microsoft.public.excel.worksheet.functions -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Frank thanks for this, i can't get that group using msnews.microsoft.com :( Cheers JulieD "Frank Kabel" wrote in message ... Hi Excel.worksheetfunctions But the solution was not really a solution: - you could gain some performance improvement by using the '--' notation (approx. 5%) - I'd better use a pivot table for this -- Regards Frank Kabel Frankfurt, Germany "JulieD" schrieb im Newsbeitrag ... Hi Frank i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
|
#10
|
|||
|
|||
|
Hi Julie,
Sorry, but I am not really an expert on the idiosyncracies of Outlook Express. You might want to check if you have the latest patches for it (I imagine you do), or check/search/ask in the NG dedicated to OE. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar no idea why i don't see all posts - microsoft.public.excel groups aren't tooo bad ... however, there's lots of times that i only see the first reply to a post rather than the posts itself - in microsoft.public.project this happens about 95% of the time. The only "solution" that i've come across is to unsubscribe and then re-subscribe on a regular basis but then i lose all my "watched" threads :( i'm using outlook express an no, no filters / rules or anything else that i can think of. if you have any ideas for fixing this i'ld be very interested Cheers julieD "Tushar Mehta" wrote in message ... It's strange you don't see all the posts. Do you have any filters that might be affecting the displayed posts? In any case and for what it's worth, the message id of the worksheet.functions post is -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar i subscribe to that group but can't see Christopher's post there so i thought that there must be another group (although i know i don't see all posts for some reason) Cheers JulieD "Tushar Mehta" wrote in message ... The newsgroup is microsoft.public.excel.worksheet.functions -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Frank thanks for this, i can't get that group using msnews.microsoft.com :( Cheers JulieD "Frank Kabel" wrote in message ... Hi Excel.worksheetfunctions But the solution was not really a solution: - you could gain some performance improvement by using the '--' notation (approx. 5%) - I'd better use a pivot table for this -- Regards Frank Kabel Frankfurt, Germany "JulieD" schrieb im Newsbeitrag ... Hi Frank i'm interested in this but can't find Christopher's other post ..what group was it in? Cheers JulieD "Frank Kabel" wrote in message ... Hi see your other post -- Regards Frank Kabel Frankfurt, Germany "Christopher Kennedy" schrieb im Newsbeitrag oups.com... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
