Formula Modification

[Tharabai]

Hi All,
Am using the below SUMIFS formula which takes very long time to display the results in the sheet which contains 15000 lines.
Can this formula be edited in a way so that the time taken to display the results reduces.

If lastrow > 2 Then
  .Range(.Range("H2"), .Range("H" & lastrow)).SpecialCells(xlCellTypeVisible).Formula = "=SUMIFS(C[-5],C[-8],RC[-3],C[-7],""<=""&RC[-2],C[-6],"">=""&RC[-2])" 
 
End If

In excel I use the formula as “=SUMIFS(D:D,A:A,F6,B:B,"<="&G6,C:C,">="&G6)”

 

[Tharabai]

Sorry this is the actual formula that needs to be edited

=SUMIFS(D:D,A:A,F2,B:B,"<="&G2,C:C,">="&G2,B:B,"<="&H2,C:C,">="&H2)

 

[Khalid NGO]

Hi,
I am not sure, but have you tried limiting your range?

=SUMIFS($D$2:$D$20000,$A$2:$A$20000,F2,$B$2:$B$20000,"<="&G2,$C$2:$C$20000,">="&G2,$B$2:$B$20000,"<="&H2,$C$2:$C$20000,">="&H2)

Regards,

 

[Deepak]

Why not using lastrow in sumif too.
You may also convert formula to value for speedup.

 

[NARAYANK991]

Hi ,

I do not know why you need to use the R1C1 style of addressing ; will the following line not work the same way ?

.Range("H2:H" & lastrow).SpecialCells(xlCellTypeVisible).Formula = "=SUMIFS(D:D, A:A,F2, B:B," & """<=""" & "&G2, C:C," & """>=""" & "&G2, B:B," & """<=""" & "&H2, C:C," & """>=""" & "&H2)"

I tried out this over 15000 rows , and it works quite fast ; the delay must be because of the rest of the worksheet and the resulting calculations ; if so , you could make it faster by using :

Application.Calculation = xlCalculationManual

before the line of code , and restore the calculation to Automatic after.

It would also be prudent to disable events using :

Application.EnableEvents = False

before the line of code , and enable them after.

Narayan

 

[Tharabai]

Yes.. but taking time even when i use enable events false.

Sub Compare_Formula()
Sheets("PM").Select
 With ActiveSheet
  lastrow = .Range("F" & Rows.Count).End(xlUp).Row
  Application.EnableEvents = False
  If lastrow > 2 Then
  .Range("H2:H" & lastrow).SpecialCells(xlCellTypeVisible).Formula = "=SUMIFS(D:D, A:A,F2, B:B," & """<=""" & "&G2, C:C," & """>=""" & "&G2, B:B," & """<=""" & "&H2, C:C," & """>=""" & "&H2)"
  End If
  ActiveSheet.Columns("H:H").Copy
  ActiveSheet.Columns("H:H").PasteSpecial xlPasteValues
  ActiveSheet.Range("H1") = "Results"
  ActiveSheet.Range("H1").Select
  Application.EnableEvents = True
 
End With
End Sub

The above is the code which i use.. when i run the code, the excel is not responding... not sure where the issue is..

Please help me on this !!

 

[Deepak]

Tharabai

Unnecessary things would force excel to hang.

this is a optimized version.

Sub Compare_Formula1()
With Sheets("PM")
  lastrow = .Range("F" & .Rows.Count).End(xlUp).Row
  Application.EnableEvents = False
  If lastrow > 2 Then
  .Range("H2:H" & lastrow).SpecialCells(xlCellTypeVisible).Formula = "=SUMIFS(D:D, A:A,F2, B:B," & """<=""" & "&G2, C:C," & """>=""" & "&G2, B:B," & """<=""" & "&H2, C:C," & """>=""" & "&H2)"
  End If
  .Range("H2:H" & lastrow).Value = .Range("H2:H" & lastrow).Value
  .Range("H1") = "Results":  .Range("H1").Select
  Application.EnableEvents = True
End With
End Sub

 

[NARAYANK991]

Hi ,

I tried your code as is , setting lastrow to 30000 ; it completed execution in a few seconds.

I am reiterating that there is nothing wrong in your code , though the entire column copy / paste could be avoided.

Have you tried setting the calculation mode to Manual ?

Upload your workbook if that is possible.

Narayan