title: Need help optimizing SUMIFS within VBA
body: <p>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.  </p>
  
  <pre><code>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
  </code></pre>