Slow time deleting filtered rows. Optimization help (sumproduct).

[Pojzon]

Hello,
I have a quite big file of 30,000+ rows,. It's full of sumproduct formulas, It basically checks prices in sheet1

and adds them up in sheet2 to check how much employee earned and based on that what is going to be his salary(10% above minimum to earn + add on).

My only problem with this file is that it takes too much time adding or deleting new row/column that's beetween formula range. For example if I would like to get rid of rows with [1]JobName1 by filtering them and deleting, it can take even up to 1 hour to do it for every single employee.
D3 formula =if(counta(D6:D15)=0;"---";sumproduct(D6:D15;Sheet1!$C$2:$C$11)).
D2 formula, big one, that is probably cause of my problems =IFERROR(IF(D3>Sheet1!$A$4;(D3-Sheet1!$A$4)*(Sheet1!$A$2/100)+(SUMPRODUCT(Sheet1!$D$2:$D$11;--NOT(ISBLANK(D6:D15)))/COUNT(D6:D15));(SUMPRODUCT(Sheet1!$D$2:$D$11;--NOT(ISBLANK(D6:D15)))/COUNT(D6:D15)));0)+IFERROR(IF(D4>Sheet1!$A$4;(D4-Sheet1!$A$4)*(Sheet1!$A$2/100)+(SUMPPRODUCT(Sheet1!$D$12:$D$21;--NOT(ISBLANK(D16:D25)))/COUNT(D16:D25));(SUMPPRODUCT(Sheet1!$D$12:$D$21;--NOT(ISBLANK(D16:D25)))/COUNT(D16:D25)));0)+IFERROR(IF(D5>Sheet1!$A$4;(D5-Sheet1!$A$4)*(Sheet1!$A$2/100)+(SUMPRODUCT(Sheet1!$D$22:$D$31;--NOT(ISBLANK(D26:D35)))/COUNT(D26:D35));(SUMPRODUCT(Sheet1!$D$22:$D$31;--NOT(ISBLANK(D26:D35)))/COUNT(D26:D35)));0)
Link to example file: https://www.dropbox.com/s/byz03rtwud0a0cf/example.xlsx?dl=0
Is there any way to make it work faster? Perhaps change sumproduct to sumif somehow? Thank you for any help.

 

[StephenCrump]

At the moment, you have formulae interspersed with data down 30,000+ rows. This makes your workbook very inflexible. Suppose you needed to introduce a new JobName11, or a new job category [4] - it would be a nightmare updating your formulae!

We can also make the formulae a little more sophisticated, e.g. to eliminate most of the (blank) rows in the data, and calculate summaries only for the job numbers provided.

The best solution depends on the results you want to see ... Do you really need to see the summary results for every employee at the same time? Or perhaps do you just want the ability to see summary results for any one chosen employee at a time? If so, we could eliminate most of the formulae in your worksheet.

Just by the way, you could simplify your formulae in Arkusz2!D2:N2. All the elements that look like this:

=IF(D3>Arkusz1!$A$4,(D3-Arkusz1!$A$4)*(Arkusz1!$A$2/100)+(SUMPRODUCT(Arkusz1!$D$2:$D$11,--NOT(ISBLANK(D6:D15)))/COUNT(D6:D15)),(SUMPRODUCT(Arkusz1!$D$2:$D$11,--NOT(ISBLANK(D6:D15)))/COUNT(D6:D15)))

can be simplified to:

=MAX(0,D3-Arkusz1!$A$4)*Arkusz1!$A$2/100+SUMPRODUCT(Arkusz1!$D$2:$D$11,--NOT(ISBLANK(D6:D15)))/COUNT(D6:D15)

It won't fix the speed of the workbook, but it is easier to follow.

 

[Pojzon]

Suppose you needed to introduce a new JobName11, or a new job category [4] - it would be a nightmare updating your formulae!

You are right, it's a pain, but I have to do it usually once a year, so it's bearable.

Unfortunely I do need to see results of every employee. I do it by filtering without zeros column "Average earned" so in the end a lot of rows are unused, not sure if it's possible to add them only when needed.
Also in this example there is only one "minimum to earn" which is set to 100, but in reality every job category may have different amount.
Thank you for this formula, looks much better, although I don't entirely know how it works.

 

[Pojzon]

I think in the end if there is no easy solution, I might just leave formulas for 1 emoployee, delete for the rest, add/delete rows I need and just fix what changed in formula and drag it down to every employee. Not perfect but always faster than waiting few hours.

 

[Fluff]

@Pojzon

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.
I have removed the duplicate posts

 

[Pojzon]

Ah, that's why I didn't see anything. I'm sorry for the trouble.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...ltered-rows-optimization-help-sumproduct.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[StephenCrump]

Unfortunely I do need to see results of every employee. I do it by filtering without zeros column "Average earned" so in the end a lot of rows are unused, not sure if it's possible to add them only when needed.

The question was whether you need to see the results for every employee at the same time?

If you do, then I suggest you consider using a Pivot Table approach (Power Pivot would be even better, but that's probably too big a step).

But perhaps, for example, you're generating end-of-year payment summaries for each employee. One way you could do this is to have a single report template with formulae to calculate results for a specified employee. Some simple VBA code could then loop through all employees and print individual payment summaries and/or save to PDF.

There are many possibilities, depending on what you need from the exercise. But the way you've set it up, with thousands of Sumproduct formulae scattered through the data, is time consuming to set up, inflexible, prone to error, and potentially very slow to run as you have discovered.

 

[Pojzon]

We only do it monthly, it's not used for generating any end-of-year summaries. I'm not the original creator of this and I don't want to change too much in it since it's been working for some time now. I just hoped that there would be an easier answer, like switching sumproduct formula to something "lighter".
Anyway, I think that I can live with deleting formulas for time of adding/deleting rows. Thank you very much for your input. I do appreciate it.