SUMPRODUCT formula making spreadsheet run REALLY SLOW!

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
567
Office Version
  1. 365
Platform
  1. Windows
At least, I assume it's that part of the formulas that's causing the problem.

I created a cover sheet for a co-worker's workbook to summarize his data from the other sheets. The main sheet that's referenced had upwards of 9,000 rows, so the SUMPRODUCT formulas on the cover sheet are very long and complicated.
When you first open the workbook, it takes a good 45-60 seconds to open! When you type in new data on one of the referenced sheets, the dreaded "spinning circle of death" appears, like it's thinking really hard and the cell with the SUMPRODUCT formula doesn't show the updated answer for quite some time. Now, if he circle is thinking and you click on a random cell, the circle stops and you can enter things in other cells.
When you save or close the workbook, once again, it takes at least 45-60 seconds - or more - for it to complete the task!

Would I be correct in thinking that the SUMPRODUCT formulas are what's causing delay? If so, is there anything to be done to move things along?

(These formulas are saving his people hours of work, so he should just suck it up, but he gets tired of waiting and staring at it and complains that its taking too long.)

Any suggestions would be greatly appreciate!

Jenny
 
I've added a new post with samples of the problem in tables, since I can't link to the workbook.

Thanks for taking a look!

Jenny
 
Upvote 0

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.
Hi Jenny,

Even though Sumproduct can be slow the main culprit is the volatile indirect for the month year dates test. Here's a summary of what you could use. Either hard code your criteria into the code or reference a cell with the criteria such as some of the B Column rows.

With the C2 & D2 setup you can choose what year & month you would like to start from. The Complete tab sheet data has been converted to a table called "Database" so that the formulas will dynamically update as new rows are added or deleted. I didn't attempt the Who's on EDI sheet as I wasn't sure of its build, if you want to post that sheet I can add the C10 formula to my mockup for you.

After the formulas spill I've made a Dropbox link to my mockup for reference, copy and paste your Complete data into my mockup and test if it suits;

Tip when working with table formulas, paste the formula into the first cell, then highlight that including all cells in the row ie C4:N4, then click after the end of the formula in the edit bar and use CTRL+enter. Note if you paste a table formula and drag across the table referencing can/will change table fields.


Book1
ABCDFLQRST
1APPROVE DATEPO #DATEDATE2VALIDATIONON EDIIMPORT FLAGSPEC CHARBULKDATE5
210/08/2017101000938911/08/20177/07/2017WRONG SUPPLIER SITEYESYESNONO19/06/2017
34/08/2017101000938772/08/201715/08/2017IMPORT ORDERNOYES
43/08/2017101000939993/08/20177/07/2017GLC NOT LOADEDYESNO
54/08/2017101000936654/08/201715/08/2017DOMESTICNONO
64/09/2017101000937775/10/20178/10/2017GLC NOT LOADEDYES
7
89/10/2017101000935558/10/20175/10/2017DOMESTICNONO
92/11/2017101000935558/10/20175/10/2017Allocation
Complete




Book1
BCDEFGHIJKLMNOP
1Criteria1YearMonth
220177
31/07/20171/08/2017Sep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Total
4Month #04111000000007
5Allocation00001000000001
6Not Yes/Blank/No Column L01010000000002
7GLC Not Loaded01100000000002
8Wrong Supplier or Domestic02010000000003
Month End Totals
Cell Formulas
RangeFormula
P4=SUM(C4:N4)
C3=DATE($C$2,$D$2,1)
C4=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0))
C5=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0),Database[VALIDATION],$B$5)
C6=SUMPRODUCT((Database[APPROVE DATE]>=C$3)*(Database[APPROVE DATE]<=EOMONTH(C$3,0))*(Database[IMPORT FLAG]="No")*(Database[ON EDI]="No"))
C7=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0),Database[VALIDATION],$B$7)
C8=SUMPRODUCT((Database[APPROVE DATE]>=C$3)*(Database[APPROVE DATE]<=EOMONTH(C$3,0))*(--(Database[VALIDATION]="Wrong Supplier Site")+(--(Database[VALIDATION]="Domestic"))))
D3=EOMONTH(C3,0)+1



https://www.dropbox.com/s/j5ftgz9jupzc4ia/Sumproduct slow sheet_zookeeper_mockup.xlsx?dl=0
 
Upvote 0
Even though Sumproduct can be slow the main culprit is the volatile indirect for the month year dates test. Here's a summary of what you could use. Either hard code your criteria into the code or reference a cell with the criteria such as some of the B Column rows.

With the C2 & D2 setup you can choose what year & month you would like to start from. The Complete tab sheet data has been converted to a table called "Database" so that the formulas will dynamically update as new rows are added or deleted. I didn't attempt the Who's on EDI sheet as I wasn't sure of its build, if you want to post that sheet I can add the C10 formula to my mockup for you.

After the formulas spill I've made a Dropbox link to my mockup for reference, copy and paste your Complete data into my mockup and test if it suits;

Tip when working with table formulas, paste the formula into the first cell, then highlight that including all cells in the row ie C4:N4, then click after the end of the formula in the edit bar and use CTRL+enter. Note if you paste a table formula and drag across the table referencing can/will change table fields.

https://www.dropbox.com/s/j5ftgz9jupzc4ia/Sumproduct slow sheet_zookeeper_mockup.xlsx?dl=0

Ooh! Very interesting! I haven't worked with databases before! How do you convert to a table with a name? And how does it become dynamic? Just by the fact of being officially a table?

I've played around and adjusted 1 or 2 of the formulas just to tweak them and am getting it figured out, but I'll probably be back with a question or two.

I can't even TELL you how thrilled I am to know about highlighting/CTRL+enter to keep the references correct! I've always just had to manually adjust them every time! (I'll bet I'm the only one around here that knows that now. I'm gonna keep it a secret and impress people later :cool: )

I have to clock out in 6 minutes and I won't be in tomorrow, but I'll get back to this on Monday.

Thank you SO much; you've made my day! Have a great weekend.

Jenny
 
Upvote 0
Hello again!

I didn't get back to this on Monday; they keep wanting me to do my regular duties before working on stuff like this. (They're no fun, LOL!)

The only adjustment I've made is changing the last reference in each formula. Column B doesn't actually show the descriptors shown; there are other titles in there. So I just made the formulas refer to the text that the formula is truly looking for.

Anyway, I've run into a couple of problems:

When I add rows to the "Complete" tab, they don't seem to get incorporated into the named table. I haven't done anything with tables before, so I have no clue how to address this. Also, how do you convert the data into a table and name it? (I've tried Googling and it just frustrated me)
------------------------------------------------------------------------------------------
Also - on the 3rd row of data on the "Month End Totals" tab:
That one needs to count everything in column Q that is NOT "YES" and is not blank + anything that has "NO" in column L UNLESS that row has already been counted because of not having "YES" or blank in Q.
I can't seem to get my head wrapped around that, no matter how long I look at it!
Let me try to describe it better -


Column A - dateColumn LColumn Qexplanation
YESEDIThis would count as 1 because of "EDI" in Q
NOYESThis would not be counted because Q says "YES" but would count as 1 because in L it says "NO"
OKAYThis would count as 1 because Q is not "YES" and is not blank
NOThis would not be counted in Q because it's blank but would count as 1 because L is "NO"
NOMERCHThis would count as 1 because Q is not "YES" and is not blank but would also count as 1 because L is "NO". However, it should only be counted once

<tbody>
</tbody>

SO, my theory has been this:
  • Count all that are not "YES" in column Q
  • Subtract from that all that are blank in column Q
***This should leave us with everything in Q that has ANYTHING besides "YES" or is blank.

  • But here's where I get tripped up - I can get an array of where column L is "NO" and get a count, but I can't wrap my brain around NOT counting where L is "NO" but the row has already been counted because of the data in Q

You've got the rest of this figured out so beautifully; hopefully this last part is possible as well.

Thank you!

Jenny
 
Upvote 0
Hello,

I would make a sample copy of your workbook to test, note that all the formulas have Table references so the data on the Complete tab needs to be converted to an excel table;

· Click any cell in the data on the Complete tab and press CTRL+T, click ok for the prompt that appears my table has headers.
· By default, excel uses a dark color scheme, when you click on a cell in the table, the table tools tab appears at the top.
· In the table tools on the far left you can edit the table name, usually it starts as Table1 but I labelled it Database in my mock-up
· Click on the drop down for table styles and click clear to remove the table color scheme.

When done correctly when you start to type data into the approve date (Column A), you'll notice that the table auto expands to include this new row. This is what makes tables dynamic and will incorporate new data when calculating the formulas. Here's a link on tables for reference.

https://www.youtube.com/watch?v=VMScRbwDSXE

Row number 3 solution;

I played with the logic for the sumproduct not yes & not blank but countif no in EDI column and I just couldn't get it to count properly.

So the easiest solution would be to run a helper column on the end of your complete data and copy and drag this formula;

=IF([@[ON EDI]]="NO",1,IF(AND([@[IMPORT FLAG]]<>"",[@[IMPORT FLAG]]<>"yes"),1,""))

So if(NO is in column L = 1, if false then if column Q is not yes or Not blank = 1, otherwise be blank). This test seems to work correctly then use the following to count the 1's from the helper test column.

=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0),Database[Helper],1)



Book1
AFLQU
1APPROVE DATEVALIDATIONON EDIIMPORT FLAGHelper
210/08/2017WRONG SUPPLIER SITEYESYES
34/08/2017IMPORT ORDERNOYES1
43/08/2017GLC NOT LOADEDYESNO1
54/08/2017DOMESTICNONO1
64/09/2017GLC NOT LOADED
7play1
89/10/2017DOMESTICNOmerch1
92/11/2017Allocation
Complete
 
Upvote 0
I know this is late to the party, but as you state, SUMPRODUCT, and INDIRECT, are very slow! Can speed up things considerably creating named ranges for data (using VBA or Excel) and then use those names in Excel formulas. Example: For 50K+ calculations, totally removing INDIRECT which was getting data from other sheets and open files, and replacing with named ranges improved calculation speed by 300+% (yep, 3+ times faster) . According to other posts, INDIRECT is volatile meaning it can cause repeated calculations. But the most punitive is that it is reported to be single threaded and incapable of using multiple processors of today's CPUs. Can't verify that but can confirm using named ranges is the way to go.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
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