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.