title: COUNTIF with table filtering, or how does the SUMPRODUCT/SUBTOTAL/OFFSET combination actually work?
body: <p>Given the following Excel table:</p>
  
  <pre class="lang-none prettyprint-override"><code>+---+--------+----+----+
  |   | A      | B  | C  |
  +---+--------+----+----+
  | 1 | Filter | V1 | V2 | &lt;-- header row of the table, with filtering option
  +---+--------+----+----+
  | 2 | F1     | x  | x  |
  | 3 | F2     | x  | y  |
  | 4 | F1     | x  | y  |
  +---+--------+----+----+
  </code></pre>
  
  <p>I need to count the number of <code>x</code> in <code>V1</code> and <code>V2</code> columns:</p>
  
  <pre class="lang-none prettyprint-override"><code>=COUNTIF(B2:C4, "x")
  </code></pre>
  
  <p>This works and returns <code>4</code>. Now, if I filter the table so that the column <code>Filter</code> only contains <code>F1</code> values:</p>
  
  <pre class="lang-none prettyprint-override"><code>+---+--------+----+----+
  |   | A      | B  | C  |
  +---+--------+----+----+
  | 1 | Filter | V1 | V2 | &lt;-- header row of the table, with filtering option
  +---+--------+----+----+
  | 2 | F1     | x  | x  |
  | 4 | F1     | x  | y  |
  +---+--------+----+----+.
  </code></pre>
  
  <p>The first formula still returns <code>4</code>. I already found how to improve it so that is takes into account the possible filters (this solution can easily be found on the Internet):</p>
  
  <pre class="lang-none prettyprint-override"><code>=SUMPRODUCT((B2:C4="x") * SUBTOTAL(3, OFFSET(B2:C4, ROW(B2:C4) - MIN(ROW(B2:C4)), 0, 1, 1)))
  </code></pre>
  
  <p>This returns <code>3</code> for the second case, as expected. The question is: how does it work? Is anyone able to give me a detailed walkthrough of the second formula?</p>