title: CountIfs horribly slow in Excel
body: <p><strong>Scenario:</strong></p>
  
  <p>I have a scenario where each account can have many customers, and each customer can have many accounts (many to many).</p>
  
  <p>I am creating a report to count how many accounts each customer has.</p>
  
  <p><img src="https://i.stack.imgur.com/eb7E8.png" alt="enter image description here"></p>
  
  <p>To do this, I am using this formula:</p>
  
  <pre><code>=COUNTIF(CustAccount[Customer ID], [@[Customer ID]])
  </code></pre>
  
  <p>This works fine. The problem is that the CustAccount table contains 65K rows. The report table contains 45K Customer IDs. It is taking more than 20 minutes to perform this calculation on a relatively slow PC.</p>
  
  <p>I am using the obvious optimizations such as:</p>
  
  <pre><code> Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual 
  </code></pre>
  
  <p>Is it looping all of the 65K rows each time to acquire a count for every row in the 45K table?
  Any ideas on what can be done to significantly improve performance?</p>