# how to reduce sheet calculation time period

#### faizee

##### Board Regular
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

##### MrExcel MVP
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

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

#### DonkeyOte

##### MrExcel MVP
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

1,081,690
Messages
5,360,614
Members
400,592
Latest member

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...