MrExcel Publishing
Your One Stop for Excel Tips & Solutions

dsum, array or vlookup


Posted by Dan on October 04, 2001 10:33 AM

I am pulling sales data from one tab to another. The sales are by month and have a region and store no. associated with it. Should I use Dsum, arrays or vlookups. Right now I have an array that is very long and it takes in inordinate amt. of time to "compute".


Posted by Aladin Akyurek on October 04, 2001 10:39 AM

Would you post the array formula that you use?

Aladin

Posted by Dan on October 04, 2001 10:57 AM

=-SUM((Raw_Data!$J$2:$J$12600='"P&L" level detail'!$I11)*(IF('"P&L" level detail'!$B$6="all",1,Raw_Data!$G$2:$G$12600='"P&L" level detail'!$B$6))*(IF('"P&L" level detail'!$D$6="all",1,Raw_Data!$H$2:$H$12600='"P&L" level detail'!$D$6))*(IF('"P&L" level detail'!$A$6="all",1,Raw_Data!$F$2:$F$12600='"P&L" level detail'!$A$6))*(IF('"P&L" level detail'!$C$6="all",1,Raw_Data!$I$2:$I$12600='"P&L" level detail'!$C$6))*(Raw_Data!$G$2:$G$12600<>"Overhead")*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600='"P&L" level detail'!$I11)*(IF('"P&L" level detail'!$B$7="all",1,Raw_Data!$G$2:$G$12600='"P&L" level detail'!$B$7))*(IF('"P&L" level detail'!$D$7="all",1,Raw_Data!$H$2:$H$12600='"P&L" level detail'!$D$7))*(IF('"P&L" level detail'!$A$7="all",1,Raw_Data!$F$2:$F$12600='"P&L" level detail'!$A$7))*(IF('"P&L" level detail'!$C$7="all",1,Raw_Data!$I$2:$I$12600='"P&L" level detail'!$C$7))*(Raw_Data!$G$2:$G$12600<>"Overhead")*(Raw_Data!AY$2:AY$12600/1000))

Posted by Dan on October 04, 2001 11:05 AM

Can you simplify this so it won't take so long?

=-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$B$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$B$7,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$A$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$D$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))

Posted by Aladin Akyurek on October 04, 2001 11:22 AM

I suspect that the IFs are unnecessary in this array formula. Try the following SUMPRODUCT equivalent. However, don't expect too much performance gain.

=-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$B$6)*(Raw_Data!AY$2:AY$12600/1000))-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$B$7)*(Raw_Data!AY$2:AY$12600/1000))-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$A$6))*(Raw_Data!AY$2:AY$12600/1000))-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$D$6)*(Raw_Data!AY$2:AY$12600/1000))

Would you report back how it does?

Aladin

Posted by Barrie Davidson on October 04, 2001 12:37 PM

Dan, have you considered using a pivot table to summarize your data?

BarrieBarrie Davidson