title: Using COUNTIFS for a series of values at once
body: <p>Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with:</p>
  
  <ul>
  <li>Column A: rows with values from 1 to 3</li>
  <li>Column B: rows with values from 1 to 250.</li>
  </ul>
  
  <p>For this purpose lets say, we are looking at all cells of value "1" in column A, that suit value "5" in column B. To find all matches, we'd use COUNTIFS command.</p>
  
  <pre><code>1   1
  2   5
  1   5
  1   7
  1   10
  3   45
  2   12
  1   2
  2   1
  
  =COUNTIFS(A1:A9;1;B1:B9;5)
  </code></pre>
  
  <p>The answer here is 1.</p>
  
  <p>Next thing, the "5" in column B belongs to a group, e.g. group from 1 to 9. What would the best way be, to count all the matches in this example, so that for all "1"'s in column A, we'd have to find all matches with values from 1 to 9 in column B?! In the upper example that would result in "4". The obvious solution is with a series of IF commands, but that's unefficient and it easy to make a mistake, that get's easily overseen.</p>
  
  <pre><code>=COUNTIFS(A1:A9;1;B1:B9;"&lt;="&amp;9)
  </code></pre>
  
  <p>Works only as the upper limit. If I give the third criteria range and condition as ">="&amp;1 it does not work - returns 0.</p>
  
  <p>Gasper</p>