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

#### taleye

##### Active Member
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!

### 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
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.