Formulas slowing down spreadsheet

[Corleone]

Good morning
I have a spreadsheet with a number of tabs on it
I recently changed the formula set up on one of the tabs so that when the main tab containing activities was filtered, the graphs would then reflect the filtered data.

Im sure there are other ways i could set it up so the graphs dynamically, but for now im looking for a solution without having to change the set up too much.

Anyway.. using the formulas below work perfectly fine, however it slows the spreadsheet down significantly with the calculation processes it now has to run through (there are around 14,000 of these formulas on the sheet)
Is there a way of producing the same formula but one which runs quicker

=SUMPRODUCT(SUBTOTAL(3,OFFSET(ACTIVITIES!$C$2,ROW(ACTIVITIES!$C$2:$C$5000)-ROW(ACTIVITIES!$C$2),0,1)),--(ACTIVITIES!$C$2:$C$5000=$A3),--(ACTIVITIES!$N$2:$N$5000=C$1))

Im using Excel 2010

Thanks

 

[RoryA]

Add a new column on the ACTIVITIES sheet that does the Subtotal part:

=SUBTOTAL(3,C2)

and then use that column (let's say it's column Z) in your SUMPRODUCT formula so that you can at least skip the volatile OFFSET part:

=SUMPRODUCT(ACTIVITIES!$Z$2:$Z$5000,(ACTIVITIES!$C$2:$C$5000=$A3)*(ACTIVITIES!$N$2:$N$5000=C$1))

 

[Corleone]

Perfect
Thanks RoryA