title: VBA - sumifs in for loop - is there a more efficient way?
body: <p>I am trying to build a macro that calculates several sumifs, looking for conditions on a different worksheet. This is the code I have so far:</p>
  
  <pre><code>Sub SumPerYear()
  
  Dim NoClients As Long
  NoClients = Worksheets("Temp").Range("A2").End(xlDown).Row - 1
  
  Sheets("Temp").Activate
  
  For i = 2 To NoClients + 1
  
      'Fill 2015 € in column E
      Cells(i, 5).Value2 = Application.SumIfs(Worksheets("Q ALL").Range("I:I"), _
          Worksheets("Q ALL").Range("A:A"), 2015, _
          Worksheets("Q ALL").Range("D:D"), Worksheets("Temp").Range("A" &amp; i).Value2, _
          Worksheets("Q ALL").Range("C:C"), True)
  
      'Fill 2015 # in column F
      Cells(i, 6).Value2 = Application.CountIfs( _
          Worksheets("Q ALL").Range("A:A"), 2015, _
          Worksheets("Q ALL").Range("D:D"), Worksheets("Temp").Range("A" &amp; i).Value2, _
          Worksheets("Q ALL").Range("C:C"), True)
  
      'Fill 2016 € in column G
      Cells(i, 7).Value2 = Application.SumIfs(Worksheets("Q ALL").Range("I:I"), _
          Worksheets("Q ALL").Range("A:A"), 2016, _
          Worksheets("Q ALL").Range("D:D"), Worksheets("Temp").Range("A" &amp; i).Value2, _
          Worksheets("Q ALL").Range("C:C"), True)
  
      'Fill 2016 # in column H
      Cells(i, 8).Value2 = Application.CountIfs( _
          Worksheets("Q ALL").Range("A:A"), 2016, _
          Worksheets("Q ALL").Range("D:D"), Worksheets("Temp").Range("A" &amp; i).Value2, _
          Worksheets("Q ALL").Range("C:C"), True)
  
      'Fill 2017 € in column I
      Cells(i, 9).Value2 = Application.SumIfs(Worksheets("Q ALL").Range("I:I"), _
          Worksheets("Q ALL").Range("A:A"), 2017, _
          Worksheets("Q ALL").Range("D:D"), Worksheets("Temp").Range("A" &amp; i).Value2, _
          Worksheets("Q ALL").Range("C:C"), True)
  
      'Fill 2017 # in column J
      Cells(i, 10).Value2 = Application.CountIfs( _
          Worksheets("Q ALL").Range("A:A"), 2017, _
          Worksheets("Q ALL").Range("D:D"), Worksheets("Temp").Range("A" &amp; i).Value2, _
          Worksheets("Q ALL").Range("C:C"), True)
  
      'Fill Tot € in column K
      Cells(i, 11).Value2 = Cells(i, 5) + Cells(i, 7) + Cells(i, 9)
  
      'Fill Tot # in column L
      Cells(i, 12).Value2 = Cells(i, 6) + Cells(i, 8) + Cells(i, 10)
  
  Next i
  
  End Sub
  </code></pre>
  
  <p>This code works, but because there are thousands of records, it takes a long time to complete. Is there a way to make this more efficient/faster?</p>
  
  <p>Thanks for your input!</p>