title: Search to see if text exists in a column, if it does then count the row
body: <p>I have 2 worksheets in Excel: </p>
  
  <p><strong>Plot</strong></p>
  
  <pre><code>plot    region  jobCount    
  1       MK      #VALUE! 
  2       MK      #VALUE! 
  3       MK      #VALUE!
  4       MK      #VALUE! 
  5       MK      #VALUE!
  6       MK      #VALUE! 
  7       MK      #VALUE!
  8       MK      #VALUE!
  9       MK      #VALUE!
  10      MK      #VALUE! 
  </code></pre>
  
  <p><strong>Waypoint</strong></p>
  
  <p><img src="https://i.stack.imgur.com/HbjM7.png" alt="enter image description here"></p>
  
  <p>How would I search column D in the <strong>Waypoint</strong> table to count where the <code>plot+region</code> (i.e. MK1, MK2...) matches the postcode and where the sequence is equal to 1.</p>
  
  <p>This is the formula I have so far:</p>
  
  <pre><code>=SEARCH(CONCATENATE(B:B,"",A:A),Waypoint!D:D,"0")
  </code></pre>
  
  <p>but it keeps returning #VALUE in the <strong>Plot</strong> table.</p>
  
  <p>I want it to concatenate whatever is in plot and region, then check column D in <strong>Waypoint</strong> to see if there are any matches, if there is a match is the value in sequence = 1, if so then count it... (sorry if this pseudocode is not clear enough)</p>
  
  <p>e.g. in the example above</p>
  
  <ul>
  <li>For plot 1, there are 2 matches so jobCount = 2 </li>
  <li>For plot 2, there is 1 match so jobCount = 1 </li>
  <li>For plot 4, there are 1 matches so jobCount = 1</li>
  </ul>
  
  <p>I hope it makes sense, I've tried very hard to be clear!</p>