Combine multiple sumproducts to speed up sheet

Hi,

I have a sheet used to calculate the progress percentages.
The structure is divided into two parts (65/35) and have separate elements in each part
I am using separate sum-products for each row and i have 100s of such rows in my sheet which is slowing the sheet.

Is there an elegant way of achieving this?

File attached for reference

I have to say, looking at the calculation itself it's unlikely to cause a big drag on performance.

Are you using Conditional Formatting, and/or other functions elsewhere such as OFFSET, INDIRECT ?

If you want to be sure of whether or not the SUMPRODUCTs are, themselves, the problem - you could temporarily insert some code like below:

Please Login or Register  to view this content.

Put XL into Manual Calc mode, and then select your SUMPRODUCT range and run the above macro (via ALT + F8), the Debug.Window in VBE will show you how long it took to calculate.

I applied the above to your sample, having extended the examples for a few thousand rows, and the calculation time is very small.

Hi Xlent,

Yes I have conditional formatting in the sheet.
Maybe its causing the drag.

Yes, quite possibly, conditional formatting is classed as "super volatile"

Charles Williams has a superb site covering XL optimisation, if you ever have the time I would suggest reading -- below link references the above point:

http://www.decisionmodels.com/calcsecretsi.htm