Workbook optimisation

The attached debt collection workbook contains many SUMPRODUCT formulas which slow it down considerably. I am looking for a method that will allow me to retain the Forecast worksheet in exactly this format while making the calculations on both worksheets much more efficient. The reason for keeping the Forecast tab format is that I need to keep and add to notes beside the specific customer - I can't afford for the notes to end up on a different row against a different customer.

I have the workbook open all day and it impacts on the performance of other workbooks also.

Thanks for looking! I will be most grateful for any suggestions.

Did you try SUMIF?
Did some experimenting with that and it speeded up noticably.

@Froment: Yes, some conditional formatting can indeed reduce performance too.

Wow! Lots of ideas here. Thanks everyone - I will try your suggestions and get back to you if I'm still looking for an answer.

Thanks everyone.

I've changed the formulas on the Forecast tab to SUMIF and cleaned up my conditional formatting rules. The performance has certainly improved.

Unfortunately, SUMIF is behaving badly. In the attachment you can see that the sums being brought in from the InvoiceData tab are all one row out of sync.

Can anyone suggest why or how I can fix this please?

Thanks again in advance!!!

The formula itself is out-of-sync.

Formula:

Please Login or Register  to view this content.

The highlighted (red) ranges should be dimensioned equally.
And you don't need the =-sign
Change the formula to

Formula:

Please Login or Register  to view this content.

and you'll be fine.

Other tip:
try not to use too many extra rows. This makes the formula go through a lot of empty cells slowing down the calculations.
Depending on how you collect the invoice data there are techniques to make the range to use more flexible.

Originally Posted by Tsjallie

Change the formula to

Formula:

Please Login or Register  to view this content.

and you'll be fine.

Other tip:
try not to use too many extra rows. This makes the formula go through a lot of empty cells slowing down the calculations.
Depending on how you collect the invoice data there are techniques to make the range to use more flexible.

You're a gem Tsjallie! Thanks again for your help. My workbook is considerably faster and is now accurate as well. Pls accept another rep star.

Oops! Add Rep won't allow me to give you more until I give some to others. Here's a virtual one - *.

Glad I could help.
Virtual rep appreciated as much as a real one