SUMPRODUCT formula making spreadsheet run REALLY SLOW!

[zookeepertx]

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

 

[zookeepertx]

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

 

[RasGhul]

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
	A	B	C	D	F	L	Q	R	S	T
1	APPROVE DATE	PO #	DATE	DATE2	VALIDATION	ON EDI	IMPORT FLAG	SPEC CHAR	BULK	DATE5
2	10/08/2017	10100093891	1/08/2017	7/07/2017	WRONG SUPPLIER SITE	YES	YES	NO	NO	19/06/2017
3	4/08/2017	10100093877	2/08/2017	15/08/2017	IMPORT ORDER	NO	YES
4	3/08/2017	10100093999	3/08/2017	7/07/2017	GLC NOT LOADED	YES	NO
5	4/08/2017	10100093665	4/08/2017	15/08/2017	DOMESTIC	NO	NO
6	4/09/2017	10100093777	5/10/2017	8/10/2017	GLC NOT LOADED	YES
7
8	9/10/2017	10100093555	8/10/2017	5/10/2017	DOMESTIC	NO	NO
9	2/11/2017	10100093555	8/10/2017	5/10/2017	Allocation
Complete

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Criteria1	Year	Month
2		2017	7
3		1/07/2017	1/08/2017	Sep 17	Oct 17	Nov 17	Dec 17	Jan 18	Feb 18	Mar 18	Apr 18	May 18	Jun 18	Jul 18	Total
4	Month #	0	4	1	1	1	0	0	0	0	0	0	0	0	7
5	Allocation	0	0	0	0	1	0	0	0	0	0	0	0	0	1
6	Not Yes/Blank/No Column L	0	1	0	1	0	0	0	0	0	0	0	0	0	2
7	GLC Not Loaded	0	1	1	0	0	0	0	0	0	0	0	0	0	2
8	Wrong Supplier or Domestic	0	2	0	1	0	0	0	0	0	0	0	0	0	3
Month End Totals

Cell Formulas
Range	Formula
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

 

[zookeepertx]

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 )

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

 

[RasGhul]

You're welcome thanks for the feedback

 

[zookeepertx]

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 -


[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]Column A - date[/TD]
[TD="align: center"]Column L[/TD]
[TD="align: center"]Column Q[/TD]
[TD="align: center"]explanation[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YES[/TD]
[TD]EDI[/TD]
[TD]This would count as 1 because of "EDI" in Q[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NO[/TD]
[TD]YES[/TD]
[TD]This would not be counted because Q says "YES" but would count as 1 because in L it says "NO"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OKAY[/TD]
[TD]This would count as 1 because Q is not "YES" and is not blank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD]This would not be counted in Q because it's blank but would count as 1 because L is "NO"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NO[/TD]
[TD]MERCH[/TD]
[TD]This 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[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[RasGhul]

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
	A	F	L	Q	U
1	APPROVE DATE	VALIDATION	ON EDI	IMPORT FLAG	Helper
2	10/08/2017	WRONG SUPPLIER SITE	YES	YES
3	4/08/2017	IMPORT ORDER	NO	YES	1
4	3/08/2017	GLC NOT LOADED	YES	NO	1
5	4/08/2017	DOMESTIC	NO	NO	1
6	4/09/2017	GLC NOT LOADED
7				play	1
8	9/10/2017	DOMESTIC	NO	merch	1
9	2/11/2017	Allocation
Complete

 

[toliphint]

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.