Slow calculation/sheet performance when using multiple SUMPRODUCT

Hi All,

I currently have approx. 2,500 instances of the below formula which has resulted in the sheet calculation being extremely slow. 2,500 formula currently provides me with a year of projection for this particular model, although ideally I would project 35 years (whole life of the asset), which would require circa 87,500 formula, although this would takes minutes for the sheet to recalculate. Is there a more efficient formula to replace the below? Is there a way use use the OR function on the output instead of repeating it, i.e. SUMPRODUCT(...=OR(0,J9,J10),TRUE,FALSE)

=IF(OR(
SUMPRODUCT((Model!$F$24:$M$9999>=J$3)*(Model!$F$24:$M$9999<K$3)*(Model!$F$24:$M$9999<$H11)*(Model!$C$24:$C$9999=$G11)*Model!$E$24:$E$9999)*2=0,
SUMPRODUCT((Model!$F$24:$M$9999>=J$3)*(Model!$F$24:$M$9999<K$3)*(Model!$F$24:$M$9999<$H11)*(Model!$C$24:$C$9999=$G11)*Model!$E$24:$E$9999)*2=J9,
SUMPRODUCT((Model!$F$24:$M$9999>=J$3)*(Model!$F$24:$M$9999<K$3)*(Model!$F$24:$M$9999<$H11)*(Model!$C$24:$C$9999=$G11)*Model!$E$24:$E$9999)*2=J10),"",
SUMPRODUCT((Model!$F$24:$M$9999>=J$3)*(Model!$F$24:$M$9999<K$3)*(Model!$F$24:$M$9999<$H11)*(Model!$C$24:$C$9999=$G11)*Model!$E$24:$E$9999)*2)

Any help would be greatly appreciated

How about

Formula:

Please Login or Register  to view this content.

@Fluff13, thank you. This has certainly made a big improvement on the calculation speed/performance, reducing the recalculation from approx. 23 seconds to 10 seconds. Although as I expand the worksheet to include 10,000's additional instances of this formula I still have concerns over performance. Would you suggest VBA is a better route or is there another performance tool/tip that I might be overlooking?

If not, I shall mark this thread as solved as you fantastic formula has dramatically improved by results.

You might be able to improve things using filter, rather than sumproduct, but I would need to see a sample file.

I have attached a sample file that contains a smaller extract from the workbook.

Within this example file:

Sheet "Model" contains the lookup range.

Sheet "Qty - Widgets" contains the formula. Your kindly provided formula, is present in rows 5,6,8,9,11,12,14,15 and a different formula is present in rows 4,7,10,13. Within the full workbook there are many additional columns containing continuing dates and many additional rows containing additional contracts.

Thank you for your continued support

Thanks for that, try

Formula:

Please Login or Register  to view this content.

Seems to return #CALC when the LET(a) result = 0

Try adding a zero for the "if_empty" argument for the filter, like so (see RED) so in H5:

=LET(a,SUM(FILTER(Model!$C$3:$C$96,BYROW(Model!$D$3:$K$96,LAMBDA(br,SUM((br>=H$3)*(br<I$3)*(br<$F5))))*(Model!$B$3:$B$96=$E5),0))*2,IF(OR(a=0,a=H3,a=H4),"",a))

@Gregb11, thank you, this resolved the #CALC issue.

@Fluff13, this solution has been absolutely perfect, thank you soo much.

Within my workbook I extended the range to cover 12,000 cells with the below formula as a nested IF, which provided a consistent formula across the entire range, so not having different forumla on certain rows. Also I reduced the lookup range from $9999 to &329 as I had many unused cells being referenced which was certainly slowing the calculation speed. Utilising the reduced lookup range the difference in calculation speed was as per below:

SUMPRODUCT = average calculation speed of 20 seconds
LET with SUMPRODUCT = average calculation speed of 16 seconds
LET with SUM, FILTER, LAMDA = average calculation speed of 8 seconds

New formula (nested IF):
=IF($I4=$I$4,LET(a,SUM(FILTER(Model!$E$24:$E$329,BYROW(Model!$F$24:$M$329,LAMBDA(br,SUM((br>=J$3)*(br<K$3)*(br<$H4))))*(Model!$C$24:$C$329=$G4),0)),a)*2,
IF($I4=$I$5,LET(a,SUM(FILTER(Model!$E$24:$E$329,BYROW(Model!$F$24:$M$329,LAMBDA(br,SUM((br>=J$3)*(br<K$3)*(br<$H4))))*(Model!$C$24:$C$329=$G4),0))*2-J3,IF(OR(a=J2,a=J3),"",a)),
IF($I4=$I$6,LET(a,SUM(FILTER(Model!$E$24:$E$329,BYROW(Model!$F$24:$M$329,LAMBDA(br,SUM((br>=J$3)*(br<K$3)*(br<$H4))))*(Model!$C$24:$C$329=$G4),0))*2-SUM(J2:J3),IF(OR(a=J2,a=J3),"",a)),"")))

I am extremely happy with this result and can't thank you enough. I have learnt a great deal about recalculation speed, and now have the LET, FILTER and LAMDA combination in my pocket to use in the future.

In summary, for those hopefully learning from this, as I have:
1. Only reference the lookup range that is populated. In my initial post, I was using an end range of $9999 as I thought this would future-proof my workbook, although this slowed the recalculation drastically. I reduced this only to the range that was populated ($329, ex $9999).
2. The LET with SUM, FILTER, LAMDA replacement for SUMPRODUCT also vastly improved the recalculation speed.

Rep added, and thread marked as solved. THANK YOU

Glad to help & thanks for the feedback.