Would you post the array formula that you use?
Aladin
=-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))
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))
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
Dan, have you considered using a pivot table to summarize your data?
BarrieBarrie Davidson