title: COUNTIFS including entire column rather than one single item
body: <p>Goal: To run a COUNTIFS matching Sheet2!A:A to Sheet1!B:B and a completed status from sample below. The goal is for sheet 1 to have training information pulled from a database and then I need to calculate who from that database has completed a list of specific training items (listed on sheet 2).</p>
  
  <p>I have tried to set up a COUNTIFS but can only do it for each individual training item. I would have to set up a long string of these adding up and also include a user condition for the formula to work properly.</p>
  
  <p><code>COUNTIFS(Sheet1!B:B,**Sheet2!A2**,Sheet1!C:C,"complete")</code></p>
  
  <p>To save me a lot of time and potential errors, I would like to to run:</p>
  
  <p><code>COUNTIFS(Sheet1!B:B,**Sheet2!A:A**,Sheet1!C:C,"complete")</code></p>
  
  <p>However, when I enter the formula it displays an error. I also have tried to utilize pivot tables but it will not allow me to complete a COUNTIFS as a calculated field for the table. 
  I am open to using VBA, but have limited experience in it if that will solve my issue. </p>
  
  <p>Sheet 1:</p>
  
  <pre><code>Name    LO ID/Title Status
  user 1    LO 1      complete
  user 1    LO 2      complete
  user 1    LO 3      registered
  user 2    LO 1      complete
  user 2    LO 3      complete
  user 2    LO 4      complete
  </code></pre>
  
  <p>Sheet 2:</p>
  
  <pre><code>Cirricula
  LO 1
  LO 3
  LO 4
  </code></pre>
  
  <p>EDIT: Ideal output on Sheet 2:</p>
  
  <p><strong>User 1 - 1</strong> (because they only have LO1 complete, LO2 is not required in the cirricula, and LO3 is a registered status)</p>
  
  <p><strong>User 2 - 3</strong> (because they have all three items complete required in the cirricula)</p>