VBA Sumifs and woksheet function

[FryGirl]

I currently have the below and it runs fines; however, trying to see if I can speed this up. My guess was either Evaluate or the Worksheet Function might help, but I don't understand how to get the second code to work. Whatever number is produced in D2 is what the entire range shows.

I'm working off of this thread SUMIFS in VBA

Sub Test1()
    With Range("D2:G" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Formula = "=SUMIFS('Data'!$BI:$BI,'Data'!$F:$F,$C2,'Data'!$U:$U,D$1)"
        .Value = .Value
    End With
End Sub

Sub Test4()
    Range("D2:G132") = WorksheetFunction.SumIfs(Sheet3.Range("BI:BI"), Sheet3.Range("F:F"), Sheet2.Range("C2"), Sheet3.Range("U:U"), Sheet2.Range("D1"))
End Sub

 

[jasonb75]

however, trying to see if I can speed this up.

Adjusting the formula ranges so that they don't refer to entire columns would be more beneficial. Without testing, I can't remember if doing it this way works or not,

Sub Test1()
Range("D2:G" & Cells(Rows.Count, "A").End(xlUp).Row).Value = Evaluate("=SUMIFS('Data'!$BI:$BI,'Data'!$F:$F,$C2,'Data'!$U:$U,D$1)")
End Sub

Using your second method, you would need to loop in order for the relative references to adjust correctly, which will be much slower.

Things such as disabling screen updating, events, calculation etc. may help.

 

[FryGirl]

Hi Jason and thanks for the help. I'm using the entire column references as the file is already ~600k. I adjusted to the range, and it dropped by a sec. I do turn everything off at the beginning and then turn it all back on at the end. The Evaluate you offered does the same as what I had, it does not loop, so I'm left with the same number all the way down.

What's weird, I can run the first macro all by itself and it runs in less then 3 seconds, yet when I combine it into another macro, that specific portion takes 26 secs. The macro in question, I timed three parts of it. The section before the Sumifs, the Sumifs section, and then what's left.

I get
.1 secs / 26 secs / 1.2 secs

 

[jasonb75]

The Evaluate you offered does the same as what I had, it does not loop, so I'm left with the same number all the way down.

I had a felling that it would be the case, it's not a method that I tend to use and couldn't remember what the behaviour was.

Not sure why it's slowing down that much, 3 seconds seems reasonable but I can't think or any reason for it to increase to 26 seconds without seeing the full code.

 

[FryGirl]

Thanks again Jason. I moved some stuff around in the macro and now it's all sorted out and running in less than 4 seconds.

 

[DanteAmor]

In my excel with 600k records, the following macro is faster than the Test1 macro from your post # 1.
It is a longer macro but on my sheet it was faster.

Sub test5()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, lr As Long
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  lr = Sheets("Data").Range("F:BI").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  a = Sheets("Data").Range("F1:BI" & lr).Value2
  b = Range("C1:G" & Range("A" & Rows.Count).End(3).Row).Value2
  
  For i = 1 To UBound(a, 1)
    dic(a(i, 1) & "|" & a(i, 16)) = dic(a(i, 1) & "|" & a(i, 16)) + a(i, 56)
  Next
  
  For i = 2 To UBound(b, 1)
    For j = 2 To 5
      b(i, j) = dic(b(i, 1) & "|" & b(1, j))
    Next
  Next
  
  Range("C1").Resize(UBound(b, 1), 5).Value = b
End Sub

 

[FryGirl]

Thanks Dante, I'll give it a go.