how to reduce sheet calculation time period

[faizee]

Dear Sir,
Can anyone help in reducing calculation of the sheet.
i have following formulas with quantities
270 times ---------> =SUMPRODUCT(('raw data'!I4:I4116=$B3(('raw data'!I4:I4116=MONTH(N$2)&YEAR(N$2))*'raw data'!R4:R4116)

9 times ----------> =DSUM('raw data'!J3:T4117,'raw data'!O3,'manual entries'!N2:O8)

3 tmes ------------> =SUMIFS(amount_wo_taxes,products,"*design*",month_year,MONTH(C3)&YEAR(C3))

128 times -------> =SUM(((('raw data'!I4:I4116=$B3)*((('raw data'!I4:I4116=0)*(days<=15)*(status="B")*amount_w_tax)

and many vlookups.
　
right now I have only 4100 rows. but it increase every day.. my sheet is taking long.. please help me in reducing its calculation time..
Regards,

 

[DonkeyOte]

If you're using XL2007 (as implied by SUMIFS) you should be using SUMIFS instead of SUMPRODUCT & SUM Arrays* whenever/wherever possible as it is significantly more efficient.

*assumes named ranges days, status and amount_w_tax are 1x4113 in terms of their dimension

Your SUMPRODUCT does not make a great deal of sense - it appears you are conducting two tests aginst I4:I4116, why is that ?

 

[faizee]

correct formula is
=SUMPRODUCT(('raw data'!I4:I4116=$B3(('raw data'!t4:t4116=MONTH(N$2)&YEAR(N$2))*'raw data'!R4:R4116)

 

[DonkeyOte]

You should find you can revert the SUMPRODUCT to:

=SUMIFS('raw data'!R4:R4116,'raw data'!I4:I4116,$B3,'raw data'!t4:t4116,MONTH(N$2)&YEAR(N$2))

which would be more efficient