title: Optimizing Excel formulas - SUMPRODUCT vs SUMIFS/COUNTIFS
body: <p>According to a couple of web sites, SUMIFS and COUNTIFS are faster than SUMPRODUCT (for example: <a href="http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html" rel="nofollow">http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html</a>). I have a worksheet with an unknown number of rows (around 200 000) and I'm calculating performance reports with the numbers. I have over 6000 times almost identical SUMPRODUCT formulas with a couple of difference each times (only the conditions change).</p>
  
  <p>Here is an example of what I got:</p>
  
  <pre><code>=IF(AFO4&gt;0,
  (SUMPRODUCT((Sheet1!$N:$N=$A4)
  *(LEFT(Sheet1!$H:$H,2)="1A")
  *(Sheet1!$M:$M&lt;&gt;"service catalog")
  *(Sheet1!$J:$J="incident")
  *(Sheet1!$I:$I&lt;&gt;"self-serve")
  *(Sheet1!$AK:$AK=AFM$1)
  *(Sheet1!$E:$E&gt;=$E$1)
  *(Sheet1!$E:$E&lt;$E$2))
  +SUMPRODUCT((Sheet1!$AJ:$AJ=$C4)
  *(LEFT(Sheet1!$H:$H,2)="1A")
  *(Sheet1!$M:$M&lt;&gt;"service catalog")
  *(Sheet1!$J:$J="incident")
  *(Sheet1!$I:$I="self-serve")
  *(Sheet1!$AK:$AK=AFM$1)
  *(Sheet1!$E:$E&gt;=$E$1)
  *(Sheet1!$E:$E&lt;$E$2)))/AFO4,0)
  </code></pre>
  
  <p>Calculating that thing takes a little bit more than 1 second. Since I have more than 6000 of those formulas, it takes a little bit over an hour to calculate everything.</p>
  
  <p>So, I'm now looking at how I could optimize that formula. Could I convert it to SUMIFS? Would it be faster? All I'm adding up here is 0s and 1s, I'm just counting the number of rows in my data source (<code>Sheet1</code>) where the set of conditions is met. Maybe COUNTIFS would work better?</p>
  
  <p>I would appreciate any help to gain some execution time since we need to execute the formulas every month.</p>
  
  <p>I can use VBA if that helps, but I always heard that Excel formulas were usually faster.</p>