How to speed up slow processing issue

I have a file that is not that large in size (31kb) but has a number of cells that incorporate lookup validations as well as sumproduct formulas. It is designed for inventory forecasting and is meant to be used by sales reps to add the quantity and product name of what they think their client will order for the upcoming year. I saved the file in *.xlsb and it does not have any macros or array's, but I can't figure out how to speed up the processing. Every time I add a new number in the quantity or backfill column (or add a new row) it takes about 15-30 seconds to recalculate. It is quite madenning. Any insight or tips would be welcomed. Note I use a Mac and have excel version 15.15.

Test Beta Inventory System v2.1.xlsb

My goal is to ultimately upload it to Google docs so the sales reps can work on the file in a collaborative effort. That said, I am not sure Google docs will even accept or retain the formulas from an xlbs. file.

Thanks in advance.

I don't see where that named range is located. Where did you see it?

Go to the Formulas tab, then Name Manager.

Check back to my previous post as well, I've edited it with some additional observations.

Thank you. I forgot about the Named Ranges as I rarely use them. That said, I didn't realize those three name ranges were even in the file so I deleted all three.

In regards to the the data extending down to down 49941 I just used a large range number to cover any new rows/records that a sales rep added. I wanted to be sure it would calculate. In reality, I will not need more than about 1000 rows, if that. I did not think about the fact the large range could cause a slow down. I will make adjustments to mine to see how much faster I can make it on my Mac. I will respond with an update shortly.

Thank you!

That was it!! I just reduced the size of the range and it eliminated the processing issue. Thank you!!

Try this formula instead, longer but faster, without reducing number of rows.

=SUMIF('Sales Rep Forecast Input'!G$8:G$49941,$B8&"*",'Sales Rep Forecast Input'!$D$8:$D$49941)+SUMIF('Sales Rep Forecast Input'!G$8:G$49941,$B8&"*",'Sales Rep Forecast Input'!$E$8:$E$49941)

Still has a slight delay, but much quicker than sumproduct. Reducing it to around 10000 rows will be almost instant.

Thank you. Actually, just reducing the number of rows made the calculations instant so I am good to go. Thanks again!

You're welcome!

Even though you don't need it now, it could always be good to keep in mind if you need something similar in future.

Dynamic named ranges are also a good thing to consider when you don't know how many rows you will need.

Indeed. As far as Dynamic Named Ranges, I did not think about that for this project, which will be important since we will be adding rows on the fly. I have not done this before so will experiment. I will also use it for another project I am working on with the same issue.

Here's an example for you, making your original formula dynamic.

This is just one of many ways to create dynamic ranges.

Thank you!