title: Google Sheets- Countif Formula Count Cells specific text Multiple Criteria
body: <p>I currently have a google sheet in which I count values in Column L against a reference sheet 'Ref' and then against a status Column W</p>
  <pre><code>IF(AND(COUNTIF(Ref!$A$1:$A$100,L2),W2 &lt;&gt;&quot;Cancelled&quot;,W2 &lt;&gt;&quot;Postponed&quot;,W2 &lt;&gt; &quot;Dropped&quot;),&quot;Y&quot;, &quot;N&quot;)
  </code></pre>
  <p>However the values have now changed in Column L. Instead of defined values in each cell they will be a string of values in each cell, the permutations will be too great to enter into my reference sheet.</p>
  <p>How do I still count the values in column L to correctly return a 'Y' or 'N'</p>
  <p>I have tried</p>
  <pre><code>=IF(AND(COUNTIF(L2,{&quot;*LEE*&quot;,&quot;*LON*&quot;,&quot;*LAM*&quot;}),W2 &lt;&gt;&quot;Cancelled&quot;,W2 &lt;&gt;&quot;Postponed&quot;,W2 &lt;&gt; &quot;Dropped&quot;),&quot;Y&quot;, &quot;N&quot;)
  
  =IF(AND(COUNTIF(Ref!$A$1:$A$100,&quot;*&quot;&amp;L2&amp;&quot;*&quot;),W2 &lt;&gt;&quot;Cancelled&quot;,W2 &lt;&gt;&quot;Postponed&quot;,W2 &lt;&gt; &quot;Dropped&quot;),&quot;Y&quot;, &quot;N&quot;)
  
  =IF(AND(COUNTIF(L2,{&quot;*LEE*&quot;,&quot;*LON*&quot;,&quot;*LAM*&quot;}),W2 &lt;&gt;&quot;Cancelled&quot;,W2 &lt;&gt;&quot;Postponed&quot;,W2 &lt;&gt; &quot;Dropped&quot;),&quot;Y&quot;, &quot;N&quot;)
  </code></pre>
  <p>The values I which need to count will feature one of the following phrases 'LEE', 'LON','LAM'.</p>
  <p>The new values that are entered into column L will look like the following</p>
  <pre><code>&lt;Area1: Y - LEE&gt;,&lt;Area2: Y - MIL&gt;
  &lt;Area3: WF&gt;,&lt;Area4: Y - MUN&gt;
  &lt;Area1: YY - MUN&gt;,&lt;Area2: YY - LON&gt;,&lt;Area3: YY-LAM&gt;
  </code></pre>
  <p>So I need to be able to search within the cell for 1 of the 3 phrases.</p>
  <p>Any help greatly appreciated</p>
  <p>Thanks</p>