title: CountIFs in Excel whit VBA Code is very slow
body: <p>I'm try to use the VBA code for first time, I did a lot of search before to write these draft code, but one piece It isn't work well. 
  I used the Application.WorkSheetFunction.CountIfs but the code It is very slow, I need to read a Table with 140.000 row and 31 columns, the table with criteria for countif have 6000 rows and 13 columns.</p>
  
  <p>The code of Sheet2.Cells(x, 17), Sheet2.Cells(x, 14) and Sheet2.Cells(x, 17) It is wrong, but I can't found the problem</p>
  
  <p>Follow the piece of code that I use to countIfs</p>
  
  <p>x = 2
      y = Application.CountA(Range("A:A"))</p>
  
  <pre><code>Dim Submit_Date As Range
  Dim GU As Range
  Dim Legal_Entity_Country As Range
  Dim Media_Type As Range
  Dim Doc_Status As Range
  Dim Approval_Date As Range
  Dim Month_Create As Range
  Dim FY As Range
  Dim Status As Range
  
  Set Submit_Date = Range("Table1[[#All],[Submit_Date]]")
  Set GU = Range("Table1[[#All],[GU]]")
  Set Legal_Entity_Country = Range("Table1[[#All],[Legal_Entity_Country]]")
  Set Media_Type = Range("Table1[[#All],[Media_Type]]")
  Set Doc_Status = Range("Table1[[#All],[Doc_Status]]")
  Set Approval_Date = Range("Table1[[#All],[Approval_Date]]")
  Set Month_Create = Range("Table1[[#All],[Month_Create]]")
  Set FY = Range("Table1[[#All],[FY]]")
  Set Status = Range("Table1[[#All],[Status]]")
  
  For x = 2 To y
      With Application.WorksheetFunction
          Sheet2.Cells(x, 10) = _
              .Sum(.CountIfs(Month_Create, Sheet2.Cells(x, 3), _
                             FY, Sheet2.Cells(x, 5), _
                             GU, Sheet2.Cells(x, 6), _
                             Legal_Entity_Country, Sheet2.Cells(x, 7), _
                             Media_Type, Sheet2.Cells(x, 8), _
                             Doc_Status, Sheet2.Cells(x, 9), _
                             Status, Sheet2.Cells(1, 10)), _
                   .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), _
                             GU, Sheet2.Cells(x, 6), _
                             Legal_Entity_Country, Sheet2.Cells(x, 7), _
                             Media_Type, Sheet2.Cells(x, 8), _
                             Doc_Status, Sheet2.Cells(x, 9), _
                             Status, Sheet2.Cells(1, 10)), _
                   .CountIfs(Approval_Date, "&gt;" &amp; Sheet2.Cells(x, 2), _
                             Month_Create, Sheet2.Cells(x, 3), _
                             FY, Sheet2.Cells(x, 5), _
                             GU, Sheet2.Cells(x, 6), _
                             Legal_Entity_Country, Sheet2.Cells(x, 7), _
                             Media_Type, Sheet2.Cells(x, 8), _
                             Doc_Status, Sheet2.Cells(x, 9), _
                             Status, Sheet2.Cells(1, 13)), _
                   .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), _
                             Approval_Date, "&gt;=" &amp; Sheet2.Cells(x, 1), _
                             GU, Sheet2.Cells(x, 6), _
                             Legal_Entity_Country, Sheet2.Cells(x, 7), _
                             Media_Type, Sheet2.Cells(x, 8), _
                             Doc_Status, Sheet2.Cells(x, 9), _
                             Status, Sheet2.Cells(1, 13)))
           Sheet2.Cells(x, 11) = .Sum(.CountIfs(Month_Create, Sheet2.Cells(x, 3), FY, Sheet2.Cells(x, 5), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 11)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 11)))
           Sheet2.Cells(x, 12) = .Sum(.CountIfs(Month_Create, Sheet2.Cells(x, 3), FY, Sheet2.Cells(x, 5), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 12)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 12)))
  '         Sheet2.Cells(x, 13) = .Sum(.CountIfs(Month_Create, Sheet2.Cells(x, 3), Approval_Date, "&lt;=" &amp; Sheet2.Cells(x, 2), FY, Sheet2.Cells(x, 5), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 13)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), Month_Approved, Sheet2.Cells(x, 3), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 13)))
  '         Sheet2.Cells(x, 14) = .Sum(.CountIfs(Month_Create, Sheet2.Cells(x, 3), FY, Sheet2.Cells(x, 5), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 14)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), Month_Approved, Sheet2.Cells(x, 3), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 14)))
           Sheet2.Cells(x, 15) = .Sum(Sheet2.Cells(x, 10), Sheet2.Cells(x, 11), Sheet2.Cells(x, 12), Sheet2.Cells(x, 13), Sheet2.Cells(x, 14))
           Sheet2.Cells(x, 16) = .CountIfs(Month_Create, Sheet2.Cells(x, 3), FY, Sheet2.Cells(x, 5), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9))
  '         Sheet2.Cells(x, 17) = .Sum(.CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), Month_Approved, Sheet2.Cells(x, 3), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 14)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 11)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 12)))
           Sheet2.Cells(x, 18) = .Sum(.CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 10)), .CountIfs(Submit_Date, "&lt;" &amp; Sheet2.Cells(x, 1), Approval_Date, "&gt;=" &amp; Sheet2.Cells(x, 1), GU, Sheet2.Cells(x, 6), Legal_Entity_Country, Sheet2.Cells(x, 7), Media_Type, Sheet2.Cells(x, 8), Doc_Status, Sheet2.Cells(x, 9), Status, Sheet2.Cells(1, 13)))
           Sheet2.Cells(x, 19) = .Sum(Sheet2.Cells(x, 16), Sheet2.Cells(x, 17), Sheet2.Cells(x, 18)) = Sheet2.Cells(x, 15)
      End With
  Next x
  </code></pre>
  
  <p>Regards
  André</p>