SUMPRODUCT formulas slowing worksheet significantly

Hello,

I have had a workbook that I have been using to track inventory. I use a userform to enter the data for inventory in and out of the system. I recently added two more worksheets to the workbook, each of which has several SUMPRODUCT formulas on them that reference the inventory data.

Since adding these two sheets, when entering data via the userform, it takes a long time (relatively speaking) for the sheet to update with the relevant new data. I have attached a copy of the file, in hopes that someone can show me exactly what is causing the slow-down, and if there is a way that I can get the same results using different programming or formulas that will not be so slow.

The part that is slow is on the "Data" sheet. Click on the green or orange buttons to open the userform. Make a selection in each section, and enter an amount. Clicking on "Enter" will transfer the appropriate information to the worksheet. This is the part that is slow.

Thanks for any help.

Jason

First I should add - I've not vetted the VBA etc the below is more to illustrate how to avoid need for array (Sumproducts included)


Jason, when faced with this issue you will often find that

less elegant = more efficient

That it to say you will be best served using helpers enabling you to dispense with array requirement (incl. Sumproduct)

If we look first at the Sumproduct formulae present on Summary1 we can see that the key bits of info are:

Please Login or Register  to view this content.

So my advice would be simple... create a concatenation field on Data that holds these pieces of info that permit you to easily identify from one column which values should be included and which should be excluded, eg:

Please Login or Register  to view this content.

The 2 date tests will generate 0 or 1 depending on whether or not they're valid based on the date parameters specified on Summary1 sheet...

Once the above is setup you can look to dispense with Sumproduct altogether and use a SUMIF approach such that:

Please Login or Register  to view this content.

You will note the use of wildcards in our search criteria... so for values in C we're not really bothered as to whether or not the date on Data is within the boundaries specified by the date in D2 so we use a wildcard (*) -- whatever the value it's ok.... for D we're not interested in the 0/1 flag for the validity of the date on Data sheet when compared to the date specified in C2 so we switch the wildcard... because we are most definitely interested in the 2nd of the 0/1 flags as we only want to include those with a flag of 1.... I hope that makes sense.

You should find the above when copied produce the same results as the sumproduct but that performance improves.

If you're happy with this approach post back and we can deal with the 2nd sheet.

Jason, if you meant you wanted to replace the Sumproduct on Search sheet then you could do a similar thing... in this case we know the following to be important:

Please Login or Register  to view this content.

So given we have more criteria here we can't utilise the same key we created previously as it does not contain the requisite info... so we need to add a further key to Data sheet such that:

Please Login or Register  to view this content.

Again all we're doing here is building a string of the important bits of info such that we can use a standard SUMIF formula and dispose with arrays...

So once the above is set up we can go back to SEARCH sheet and alter the formulae as follows (based on my interpretation of your Sumproducts) :

Please Login or Register  to view this content.

For E we use a wildcard to discount the Profile criteria ... ie we don't care what the value is for that parameter in our key on Data sheet as it's irrelevant ... it can be anything and we'd still include the value in our summation.

I noticed a few rows previously did not have formulae - was there a reason for that -- eg B11, B12 had no formula whereas using the above B12 will generate a result of 1021 based on the search parameters present in the file you uploaded.

Again to reiterate I've not reviewed the VBA.