title: Use RANK function for cell range based on criteria in separate cell range
body: <p>I have a question regarding the RANK function in MS Excel 2010. I have a large worksheet whose rows I want to rank based on the values in a column. These values can be positive or negative. I found helpful advice <a href="http://www.mrexcel.com/forum/excel-questions/303127-ranking-positive-negative-but-not-zeros.html" rel="nofollow">here</a> which explains how to rank the values in a column while excluding all values that equal zero from the ranking and the ranking count. They use the following formula:</p>
  
  <pre><code>IF(O24&lt;0, RANK(O24,$O$24:$O$29) - COUNTIF($O$24:$O$29,0), IF(O24=0, "", RANK(O24,$O$24:$O$29)))
  </code></pre>
  
  <p>This works great, but it would be even better if I could rank the values only if a corresponding value in the same row but a different column meets certain criteria.</p>
  
  <p>Is something like this possible and how would I do it? How would I update the example formula above to make the change work? Thank you very much in advance for your help.</p>
  
  <p>P.S.: I tried putting in a table but it didn't really work, sorry...</p>