Need help optimizing SUMIFS within VBA

Question

I've been using this site for months now to help get me started in my early coding career and this is my first post here. I apologize if I missed this question while searching the forums yesterday. I had a spreadsheet with roughly 6,000 active Sumifs referencing a table of 500,000+ rows. As you can imagine, this took a moment to calculate. I've written them into VBA so they only calculate when the user chooses to do so. However, with so many sumifs, the code takes about 3-5 minutes to run through. I'm looking for ways to speed this up for a better end-user experience. I'll post how I'm performing the sumifs below. Just for some context, this is performing the sumifs on the same group of users twice, but with one different criteria. Let me know if I left out any pertinent information.

For i = 1 To 12
    Range("Emp_Utility").Offset(1, i).Activate
    Do Until Cells(ActiveCell.Row, 2) = ""
        ActiveCell.Value = Application.WorksheetFunction.SumIfs(Hrs, Emp, Cells(ActiveCell.Row, 2), Y, Cells(4, ActiveCell.Column), M, Cells(3, ActiveCell.Column))
        ActiveCell.Offset(1, 0).Activate
    Loop
Next i

For a = 1 To 12
    Range("Emp_Billable").Offset(1, a).Activate
    Do Until Cells(ActiveCell.Row, 30) = ""
        ActiveCell.Value = Application.WorksheetFunction.SumIfs(Hrs, Emp, Cells(ActiveCell.Row, 2), Y, Cells(4, ActiveCell.Column), M, Cells(3, ActiveCell.Column), Bill, "No")
        ActiveCell.Offset(1, 0).Activate
    Loop
Next a

Answer 1

Load the ranges into variant arrays and then write the SUMIFS in the code, not using formulas. If you need examples let me know and I'll guide you through it.

EDIT: No prob. Here's an example then.

Sub example()

    Dim EmpUtil, EmpBillable As Variant   ' Creates variant array

    EmpUtil = Range("Emp_Utility")        'Places Range into EmpUtil Array
    EmpBillable = Range("Emp_Billable")   'Places Range into EmpBillable Array

    For x = LBound(EmpUtil) To UBound(EmpUtil)   'Cycles through EmpUtil Rows
        'Do comparisons and additions here
        'References to arrays should be something like
        ' "EmpUtil(x,3) = example" - for the 3rd column

        'for calculations on each column you cycle through each column for that row
        For y = LBound(EmpUtil, 2) To UBound(EmpUtil, 2)
            EmpUtil(x, y) = Calculation

        Next y


    Next x

    For x = LBound(EmpBillable) To UBound(EmpBillable)   'Cycles through EmpBillable Rows
        'Do comparisons and additions here


    Next x

    Range("Emp_Utility") = EmpUtil         'Places EmpUtil Array back into Range
    Range("Emp_Billable") = EmpBillable    'Places EmpBillable Array back into Range


End Sub

This should get you started.

Answer 2

You might consider an alternative to SUMIFS (which can take forever to calculate) such as a combination of SUM and SORT. Please see the answer to How to perform SumIf using VBA on an array in Excel which looks at using a SUM and SORT combination resulting in very fast calculations whilst still returning the same values as if a SUMIFS method had been used.

Answer 3

I would not suggest to use array match method to work with big data in excel (Customers information, loan cards). You better turn off autoupdates at the begining of your code with using that sumif and then turn them on. Follow these commands: Hope that this will help!

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

<Your sumifs code>

ActiveSheet.Calculate
Application.ScreenUpdating = True
Application.DisplayAlerts = True