title: VBA Count of unique values with a second criteria
body: <p>I have hit a roadblock trying to think through how I might write the VBA code that counts the number of unique order numbers and and whether the order status is open or closed for each salesperson.</p>
  
  <p>I am working on writing the VBA that would affect Sheet2. I need the VBA to loop through Sheets("Sheet2").Range("A:A") and (1) lookup the name in Sheet1; (2a) count the unique order numbers that correspond with the salesperson's name and are "Open" and (2b) count the unique order numbers that correspond with the salesperson's name and are "Closed". I have designed "?"s for the values I am looking to calculate with the macro and included the answer numbers. </p>
  
  <p>Any help is greatly appreciated. Please let me know if I can clarify anything.</p>
  
  <p><strong>Sheet1 - Orders</strong></p>
  
  <p><img src="https://i.stack.imgur.com/Nd3HI.png" alt="enter image description here"></p>
  
  <p><strong>Sheet2 - Orders Summary</strong></p>
  
  <pre><code>       A                   B                            C    
  1  **Name**   **Count-Uniq Open Orders**   **Count-Uniq Closed Orders**
  2   John              ? (answer: 2)                ? (answer: 0)
  3   Ben               ? (answer: 1)                ? (answer: 1)
  4   Fred              ? (answer: 1)                ? (answer: 0)       
  </code></pre>