Speed up SUMPRODUCT formulae - very very slow spreadsheet HELP NEEDED!!

taleye

Active Member
Joined
Oct 22, 2008
Messages
299
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!
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
The first thing to do is to change the 10000 and 25000 to the number of actual rows, which you state to be 3365.

If the calculating time is still too large, post back here.
 

Forum statistics

Threads
1,077,991
Messages
5,337,591
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top