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

 

[RasGhul]

It may be a combination of formulas, is there any other functions like iferror or indirect? which are notorious for slowing sheets.

If the data isn't sensitive, I'm happy to look over the sheet and make suggestions?

Dropbox link?

 

[Michael M]

Is the worksheet using named ranges ?
Do they refer to entire columns rather than set ranges ?
Are the formulas referring to entire columns as well ?
They also need to be reduced where possible.

Are there any macros in the sheet module, code that may be activated when a change occurs in the sheet ?

 

[Fazza]

For sure you're correct that those formulas are the culprit, Jenny.
There are likely many alternative approaches (that are fast).
To give specific advice would require understanding of the details.

 

[zookeepertx]

I checked with our IT dept and, sadly I would not be allowed to upload a workbook, even after stripping it of information, to a dropbox. I am working on making up a table showing a very small sample of the data along with the formula on the cover sheet. I'll try to post it tomorrow.

(For some reason, my computer does NOT want to work with this message board yesterday and today! I key words and only half the letters show up and the endless "spinning circle of thinking" has been going since I signed in today. Wonder if Chrome would work any better)

Jenny

 

[Michael M]

Have you tried posting from home rather than at work ??
Your IT dept might be locking you out....you could always tell IT to fix it, but I can already guess the response.

 

[RasGhul]

Can you post an examples of the formulas?


Just as an example the *( structure version of sumproduct;

=SUMPRODUCT(($A$2:$A$10000=$E$1)*(B2:$B$10000=$D$1)*$C$2:$J$10000)

is generally slower than --( version

=SUMPRODUCT(--($A$2:$A$10000=$E$1),--(B2:$B$10000=$D$1),--$C$2:$J$10000)

But as Michael has mentioned there maybe many other formulas or VBA that are slowing down the sheet.

 

[zookeepertx]

The following table represents data on a sheet named "COMPLETE". I've included the column headers and the row numbers to (hopefully) make it easier to explain. Also note that the data headers are in row 1.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]APPROVE DATE[/TD]
[TD]PO #[/TD]
[TD]DATE[/TD]
[TD]DATE[/TD]
[TD]STATUS[/TD]
[TD]VALIDATION[/TD]
[TD]DIV[/TD]
[TD]VENDOR[/TD]
[TD]SUPP[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]ON EDI[/TD]
[TD][/TD]
[TD]SPEC ORD[/TD]
[TD][/TD]
[TD]BUYER[/TD]
[TD]IMPORT FLAG[/TD]
[TD]SPEC CHAR[/TD]
[TD]BULK[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/10/2017[/TD]
[TD]10100093891[/TD]
[TD]8/1/2017[/TD]
[TD]7/7/2017[/TD]
[TD][/TD]
[TD]WRONG SUPPLIER SITE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[TD]NO[/TD]
[TD]6/19/2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/4/2017[/TD]
[TD]10100093877[/TD]
[TD]8/1/2017[/TD]
[TD]8/15/2017[/TD]
[TD][/TD]
[TD]IMPORT ORDER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8/3/2017[/TD]
[TD]10100093999[/TD]
[TD]8/1/2017[/TD]
[TD]7/7/2017[/TD]
[TD][/TD]
[TD]GLC NOT LOADED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8/4/2017[/TD]
[TD]10100093665[/TD]
[TD]8/1/2017[/TD]
[TD]8/15/2017[/TD]
[TD][/TD]
[TD]DOMESTIC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9/4/2017[/TD]
[TD]10100093777[/TD]
[TD]10/05/2017[/TD]
[TD]10/8/2017[/TD]
[TD][/TD]
[TD]GLC NOT LOADED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]YES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10/9/2017[/TD]
[TD]10100093555[/TD]
[TD]10/08/2017[/TD]
[TD]10/5/2017[/TD]
[TD][/TD]
[TD]DOMESTIC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NO
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

***There may be blank rows on this sheet ***

Next is the cover sheet, named "MONTH END TOTALS"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=VALUE(MATCH(99^99,COMPLETE'!A:A,1))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUM(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"*MM/YYYY")=( (MONTH(C$3&1))&"/"&$C$2),1,0))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"*MM/YYYY")=((MONTH(C$3&1))&"/"&$C$2),1,0))*(--(IF((TEXT(INDIRECT("COMPLETE!$F$2:$F$"&$O$2),"@")="ALLOCATION"),1,0))))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"*MM/YYYY")=((MONTH(C$3&1))&"/"&$C$2),1,0))*(--((((IF(NOT((TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="YES")+(TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="0")),1,0)+(((IF(TEXT(INDIRECT("COMPLETE!$L$2:$L$"&$O$2),"@")="NO",1,0))))*(--(IF((TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="YES")+(TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="0"),1,0)))))))))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"*MM/YYYY")=((MONTH(C$3&1))&"/"&$C$2),1,0))*(--(IF((TEXT(INDIRECT("COMPLETE!$F$2:$F$"&$O$2),"@")="GLC Not Loaded"),1,0))))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"*MM/YYYY")=((MONTH(C$3&1))&"/"&$C$2),1,0))*(--(IF((TEXT(INDIRECT("COMPLETE!$F$2:$F$"&$O$2),"@")="WRONG SUPPLIER")+(TEXT(INDIRECT("COMPLETE!$F$2:$F$"&$O$2),"@")="DOMESTIC"),1,0))))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUMPRODUCT(--(IF(TEXT(INDIRECT("'LIST OF VENDORS WHO ARE ON EDI'!$J$2:$J$"&$O$2),"*MM/YYYY")=((MONTH(C$3&1))&"/"&$C$2),1,0))*(--(IF((TEXT(INDIRECT("'LIST OF VENDORS WHO ARE ON EDI'!$G$2:$G$"&$O$2),"@")="NEW"),1,0))))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On the TOTALS sheet:

• O2 represents the last row with data in it. The number of rows will vary depending on whether rows have been added or removed since last time the workbook was opened.
• The formulas in the table refer back to O2 very often, to denote the range of cells being looked at, so the formulas don't have to look ALL the way to the last possible row.
• Obviously, C2 is the year being evaluated. and C3 is the month being evaluated

* Cell C4 looks at the month and year - in the example, AUG 2017, and finds out how many rows with that month/year are in the range A2 through A(O2 on TOTALS) of the COMPLETE tab.
* Cell C5 finds out how many cells on COMPLETE with that month/year, in the range F2 through F(O2 on TOTALS) have the word "ALLOCATION".
* Cell C6 finds out how many of the rows on COMPLETE with that month/year meet the following criteria:

• Not "YES" in column Q
• Not blank in column Q
• "NO" in column L, only if they haven't been counted in Q by the 2 criteria above.

* Cell C7 finds out how many of the rows on COMPLETE with that month/year, in the range F2 through F(O2 on TOTALS) have the text "GLC NOT LOADED".
* Cell C8 finds out how many of the rows on COMPLETE with that month/year, in the range F2 through F(O2 on TOTALS) have either "WRONG SUPPLIER" or "DOMESTIC".
* Cell C10 looks at another tab called "LIST OF VENDORS WHO ARE ON EDI", and finds out how many of the rows on that sheet in the range J2 through J(O2 on TOTALS) with the matching month/year also have "NEW" in G of that sheet.

AND, D4 looks at the month in D3 and the year in C2 and does all the same calculations using THAT as the basis for the evaluations - in the example, SEP 2017. Same with E4, F4, G4.....

This co-worker also has multiple years on the MONTH END TOTALS sheet, so then ALL of these SUMPRODUCT formulas (most of which are also array formulas) are on that sheet WAYYY too many times. At this point, I'm sure you can imagine how slowly this thing goes!

If this can't be speeded up noticeably, he'd be okay with just having a macro to run several times a day. I had thought to use formulas to make it be up to date not matter how recently new data got entered.

So, what do you all think? Is there any hope?

Thanks,

Jenny

 

[zookeepertx]

I've made another post with samples of the problem in tables, since I can't link to the workbook.

Thanks for taking a look!

Jenny

 

[zookeepertx]

No, I don't really have any pull with IT, LOL! (Actually, he'd let me do it if it wouldn't likely get us both fired, )

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

Thanks for taking a look!

Jenny