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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top