Workbook efficiency from overuse of large sumproduct formulas

Hi,

I was wondering if you could possibly help me out. I've got multiple tabs which use large sumproduct formulas to total up results specific to each location. Unfortunately my method of working out the formulas is causing an unacceptable slow down of the document which multiple people have to use.

Is there an alternative formula /method to work out the results? Obviously switching to manual calculation is an option, but it's one I want to avoid

In the attachment is one of the tabs, all formulas have been removed except for the offending columns in yellow which is causing the slow down. I think this one would be a good one for me to learn

Thanks for any help

Have you tried it with SUMIF/SUMIFS? The formula in C7 could become:

=IF($A7="","",SUMIFS(L$7:L$1000,$I$7:$I$1000,$A7)+SUMIFS(T$7:T$1000,$Q$7:$Q$1000,$A7)+SUMIFS(AJ$7:AJ$1000,$AG$7:$AG$1000,$A7)+SUMIFS(AR$7:AR$1000,$AO$7:$AO$1000,$A7)+SUMIFS(BH$7:BH$1000,$BE$7:$BE$1000,$A7)+SUMIFS(AB$7:AB$1000,$Y$7:$Y$1000,$A7)+SUMIFS(AZ$7:AZ$1000,$AW$7:$AW$1000,$A7))

this should be faster, but I have no way of confirming it.

Hope this helps.

Pete

Hi Pete,

Thanks for the response, I tried what you suggested. The sheet is defo slightly quicker but it's still quite slow. I think it's the method I'm using of sumif+sumif+sumif+sumif which creates a huge formula resulting in the slowdown... I can't really see a way around it though

1. use Excel tables
2. change data layout