title: COUNTIF with a positive and negative criteria
body: <p>I need to count the number of cells in which the cells in "LIST A" appear in the set of values contained in column D <strong>AND</strong> the cells in "LIST B" do not appear in column E.</p>
  
  <p>I am trying to use something like the following array formula, but so far have had limited success:</p>
  
  <pre><code>={SUM(COUNTIFS(A2:A21,D2:D5,B2:B21,"&lt;&gt;"&amp;E2:E3))}
  </code></pre>
  
  <p>Please note that the data contained in this example is different to the data in my real table. The real table is considerably longer and more complex than this table.</p>
  
  <p>Any suggestions?</p>
  
  <p><a href="https://i.stack.imgur.com/CrUBa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CrUBa.png" alt="Example"></a></p>