title: SUMIFS formulas are somewhat slow
body: <p>I am trying to create some sumifs formulas. The raw data has three columns: one for Batch ID, the second for Dates and the third for Amounts. I have used a helper column to get the month and the year in column O (to match them with the headers in another sheet)
  Here's my attempt</p>
  <pre><code>Sub Test()
      Dim sBatchCol As String, sDates As String, sAmount As String, sDateTarget As String, lr As Long, m As Long, c As Long
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
          With shPayment
              lr = .Cells(Rows.Count, 1).End(xlUp).Row
              With .Range(&quot;O2:O&quot; &amp; lr)
                  .Formula = &quot;=&quot;&quot;'&quot;&quot; &amp; CHOOSE(MONTH(J2),&quot;&quot;Jan&quot;&quot;,&quot;&quot;Feb&quot;&quot;,&quot;&quot;Mar&quot;&quot;,&quot;&quot;Apr&quot;&quot;,&quot;&quot;May&quot;&quot;,&quot;&quot;Jun&quot;&quot;,&quot;&quot;July&quot;&quot;,&quot;&quot;Aug&quot;&quot;,&quot;&quot;Sep&quot;&quot;,&quot;&quot;Oct&quot;&quot;,&quot;&quot;Nov&quot;&quot;,&quot;&quot;Dec&quot;&quot;)&amp;&quot;&quot;-&quot;&quot;&amp;YEAR(J2)&quot;
                  .Value = .Value
              End With
              sBatchCol = .Range(&quot;D2:D&quot; &amp; lr).Address(, , , True)
              sDates = .Range(&quot;O2:O&quot; &amp; lr).Address(, , , True)
              sAmount = .Range(&quot;K2:K&quot; &amp; lr).Address(, , , True)
          End With
          With shMonthlyFunds
              m = .Cells(Rows.Count, 1).End(xlUp).Row - 1
              For c = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
                  If .Cells(1, c).Value Like &quot;???-####&quot; Then
                      sDateTarget = .Cells(1, c).Address
                      With .Range(.Cells(2, c), .Cells(m, c))
                      .Formula = &quot;=SUMIFS(&quot; &amp; sAmount &amp; &quot;,&quot; &amp; sBatchCol &amp; &quot;,A2,&quot; &amp; sDates &amp; &quot;,&quot; &amp; sDateTarget &amp; &quot;)&quot;
                      End With
                  End If
              Next c
          End With
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
  End Sub
  </code></pre>
  <p>The code is working but it is quite slow. I tried to turn off the calculation but the same problem is still there.</p>