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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,073
Messages
5,412,202
Members
403,422
Latest member
MARKO1958

This Week's Hot Topics

Top