title: Using INDEX within a COUNTIF
body: <p>I'm trying to combine a couple of working formulae in Excel into a single formula to do a <code>lookup</code> and <code>countif</code>.</p>
  
  <p>Currently I use :</p>
  
  <pre><code>=IF(I$7&lt;&gt;"",IF(INDEX('OtherWorkSheet'!$H:$H,I$8)="Yes","Y","N"),"")
  </code></pre>
  
  <p>to lookup if a value in a particular column (<code>H</code>) in a different work sheet is set to 'Yes' for a particular row selected using <code>INDEX</code> and a value in the <code>I$8</code> in the example above.</p>
  
  <p>I am also using:</p>
  
  <pre><code>=COUNTIF('OtherWorkSheet'!$G17:$H17,"Yes")+COUNTIF('OtherWorkSheet'!$J17:$L17,"Yes")
  </code></pre>
  
  <p>to count up the number of columns that are set to 'Yes'.</p>
  
  <p>I'm trying to combine the two so that I am counting the number of columns set to <code>Yes</code> for a given value of <code>I$8</code>.</p>
  
  <p>(Please don't ask why! This is part of a much bigger complex spreadsheet and this is the one thing I can't get working. A full redesign of the rest of the spreadsheet is not an option, unfortunately)</p>
  
  <p>The formula I have come up with is:</p>
  
  <pre><code>=IF(countif(INDEX('OtherWorkSheet'!$G:$H,I$8),"Yes")+countif(INDEX('OtherWorkSheet'!$J:$L,I$8),"Yes") &gt; 1, "Y", "N" )
  </code></pre>
  
  <p>But this gives me reference errors as <code>countif</code> doesn't seem to like the result of <code>index</code> in its array argument.</p>
  
  <p>If I use:</p>
  
  <pre><code>=IF(countif(INDEX('OtherWorkSheet'!$G:$G,I$8),"Yes")+INDEX('OtherWorkSheet'!$H:$H,I$8),"Yes")+INDEX('OtherWorkSheet'!$J:$J,I$8),"Yes")+INDEX('OtherWorkSheet'!$K:$K,I$8),"Yes")+countif(INDEX('OtherWorkSheet'!$L:$L,I$8),"Yes")) &gt; 1, "Y", "N" )
  </code></pre>
  
  <p>then everything works, but this seems a bit cumbersome...</p>
  
  <p>Can the formula be simplified and stillwork? (And still be copy and pasteable?)</p>