VBA SumIfs Too Slow

Question

I have a WorksheetFunction.SumIfs with 3 Args code being applied in so many cells (10k rows x 20 columns), it ran for 2 hours to get complete, but when I do the same but with formula in excel and drag and drop until last column and line, it goes much faster (less than 10min). I have already done xlCalculationManual. do you have any idea on how to improve processing time in VBA?

Code:

application.calculation= xlCalculationManual

for Col = 3 to 22
   for Row = 2 to 10000
      FileA.Cells(Row, Col).Value = Application.WorksheetFunction.SumIfs(FileB.Range("A:A"), FileB.Range("D:D"), FileA.Range("A" & Row).Value, FileB.Range("B:B"), FileA.Range("B" & Row).Value, FileB.Range("C:C"), FileA.Cells(1, Col).Value)
   Next
Next

SOLUTION: I found a simple solution by myself. In a big range of data, instead of using Application.WorksheetFunction.FUNCTION_NAME inside FOR, use Book.Sheet.Range().Formula = "=Formula(Parameters)" in the first Cell, then use .Copy, then .PasteSpecial Paste:=xlPasteFormulas, examples below:

' Takes 2h
for Col = 3 to 22
   for Row = 2 to 10000
      FileA.Cells(Row, Col).Value = Application.WorksheetFunction.SumIfs(FileB.Range("A:A"), FileB.Range("D:D"), FileA.Range("A" & Row).Value, FileB.Range("B:B"), FileA.Range("B" & Row).Value, FileB.Range("C:C"), FileA.Cells(1, Col).Value)
   Next
Next

' Takes 10min
application.calculation= xlCalculationManual

FileA.Cells(2, 3).Formula = "=SUMIFS([FileB.XLSX]Sheet1!$A:$A,[FileB.XLSX]Sheet1!$D:$D,$A2,[FileB.XLSX]Sheet1!$B:$B,$B2,[FileB.XLSX]Sheet1!$C:$C,C$1)"
FileA.Cells(2, 3).Copy
FileA.Range(FileA.Cells(2, 3), FileA.Cells(10000, 22)).PasteSpecial Paste:=xlPasteFormulas

application.calculation= xlCalculationAutomatic

Check out this Q&A. Specifically the `application.screenupdating` and `application.calculate` settings provided in some of the answers: [How to improve the speed of VBA macro code?](https://stackoverflow.com/questions/13016249/how-to-improve-the-speed-of-vba-macro-code) Basically you want to shut off excel's updating in the front end so it can iterate through your neste `for` loop as fast as possible, and then turn all of those features back on again once it's finished. — JNevill, Oct 26 '21 at 19:05
1. limit all the full columns to just the dataset, you can find that dynamically earlier in the code then use just that range. 2. For the other inputs, fill an array of data and iterate those instead of ranges. 3. Fill a variant array with the outputs and then load the range at one time. — Scott Craner, Oct 26 '21 at 19:06
@ScottCraner, I couldn't understand your solution in practice, could you please do an example with my code? Thanks in advance — Munir Sella Bakkar, Oct 26 '21 at 19:35
also col starting at 1 does not make sense as you are referring to B on that same sheet, but starting in A, would that not overwrite the very value you are using for the sumif? — Scott Craner, Oct 26 '21 at 19:35
@ScottCraner, the code is just and an example, it is for my job and i can't put the real code (that works) — Munir Sella Bakkar, Oct 26 '21 at 19:40
@ScottCraner, ok I edited it so it is real, like the code in my job — Munir Sella Bakkar, Oct 26 '21 at 19:42
Have you tried queries? This sounds like a classical left outer scenario and will run within a minute. — Nick van H., Oct 26 '21 at 19:52
Bit more complex but faster: https://stackoverflow.com/questions/64939776/faster-way-of-using-sumifs/64940481#64940481 (example runs in <1sec for 100k rows) Also handles countifs/averageifs — Tim Williams, Oct 26 '21 at 20:13
@ScottCraner, I found a simples solution and editted it in the Question, please check it — Munir Sella Bakkar, Oct 28 '21 at 12:56

Scott Craner · Answer 1 · 2021-10-26T22:14:16.300

As per my comments, use variant arrays and loop the range once.

Sub mysumif()
    Dim fileA As Worksheet
    Set fileA = Worksheets("Sheet2")
    
    Dim fileB As Worksheet
    Set fileB = Worksheets("Sheet1")
    
    Dim rngArr As Variant
    rngArr = Intersect(fileB.Range("A:D"), fileB.UsedRange)
    
    Dim Bclm As Variant
    Bclm = Intersect(fileA.Range("A2:B100000"), fileA.UsedRange)
    
    Dim ttlRos As Variant
    ttlRos = Intersect(fileA.Range("C1:ZZ1"), fileA.UsedRange)
    

    
    Dim otptArr As Variant
    ReDim otptArr(1 To UBound(Bclm, 1), 1 To UBound(ttlRos, 2))
    
    Dim i As Long
    For i = 1 To UBound(rngArr, 1)
        Dim j As Variant
        j = Application.Match(rngArr(i, 3), ttlRos, 0)
        
        If Not IsError(j) Then
            Dim k As Long
            For k = 1 To UBound(Bclm, 1)
                If Bclm(k, 1) = rngArr(i, 4) And Bclm(k, 2) = rngArr(i, 2) Then
                    otptArr(k, j) = otptArr(k, j) + rngArr(i, 1)
                    Exit For
                End If
            Next k
        End If
    Next i
    
    fileA.Range("C2").Resize(UBound(otptArr, 1), UBound(otptArr, 2)).Value = otptArr
                
End Sub

Before:

After:

Also note that a pivot table can do this also much quicker:

VBA SumIfs Too Slow

1 Answers1