My spreadsheet is currently taking over 15 minutes to calculate and this is way too slow
I have many SUMPRODUCT formula and believe these are slowing the process down massively
Is there another formula I could use instead that would be quicker?
If I post a couple of my forumulae could somebody please please have a quick look at them to reduce bottlenecks?!!
In desparate need of help
=SUMPRODUCT(--('[MRP Data File.xls]Z1INV'!$A$1:$A$10000=$A3),--('[MRP Data File.xls]Z1INV'!$E$1:$E$10000="15BG"),'[MRP Data File.xls]Z1INV'!$F$1:$F$10000)
=SUMPRODUCT(--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$A$1:$A$10000=A3),--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$D$1:$D$10000="15BG"),'[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$R$1:$R$10000)
=SUMPRODUCT(--('[MRP Data File.xls]MB51'!$C$1:$C$25000=$A3),--('[MRP Data File.xls]MB51'!$H$1:$H$25000>=EDATE($C$1,-3)),'[MRP Data File.xls]MB51'!$I$1:$I$25000)*-1
=SUMPRODUCT(--('[MRP Data File.xls]MB51'!$C$1:$C$25000=$A3),--('[MRP Data File.xls]MB51'!$H$1:$H$25000>=EDATE($C$1,-6)),'[MRP Data File.xls]MB51'!$I$1:$I$25000)*-1
These forumlas are in 7 columns and go 3365 rows down!
I have many SUMPRODUCT formula and believe these are slowing the process down massively
Is there another formula I could use instead that would be quicker?
If I post a couple of my forumulae could somebody please please have a quick look at them to reduce bottlenecks?!!
In desparate need of help
=SUMPRODUCT(--('[MRP Data File.xls]Z1INV'!$A$1:$A$10000=$A3),--('[MRP Data File.xls]Z1INV'!$E$1:$E$10000="15BG"),'[MRP Data File.xls]Z1INV'!$F$1:$F$10000)
=SUMPRODUCT(--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$A$1:$A$10000=A3),--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$D$1:$D$10000="15BG"),'[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$R$1:$R$10000)
=SUMPRODUCT(--('[MRP Data File.xls]MB51'!$C$1:$C$25000=$A3),--('[MRP Data File.xls]MB51'!$H$1:$H$25000>=EDATE($C$1,-3)),'[MRP Data File.xls]MB51'!$I$1:$I$25000)*-1
=SUMPRODUCT(--('[MRP Data File.xls]MB51'!$C$1:$C$25000=$A3),--('[MRP Data File.xls]MB51'!$H$1:$H$25000>=EDATE($C$1,-6)),'[MRP Data File.xls]MB51'!$I$1:$I$25000)*-1
These forumlas are in 7 columns and go 3365 rows down!