title: Count unique values in excel based on multiple criteria
body: <p>I've got a spreadsheet with 3 columns and want a formula to count the unique values in column A based on 3 criteria against B and C.</p>
  
  <p>Below is an example of the data and desired output. E2:H2 is where the formula should go. The data contains blank values in column C. Column B is the result of a vlookup.</p>
  
  <pre><code>   A        B        C        D        E        F        G        H
  1  Email    List     Date              1/1/19   1/2/19   1/3/19   1/4/19
  2  1@1.com  X        1/1/19            2        1        1        0
  3  2@2.com  Y        6/3/19
  4  3@3.com  Z        2/2/18
  5  1@1.com  X        9/1/19
  6  4@4.com  X        5/2/19
  7  1@1.com  X        
  8  5@5.com  X        4/1/19
  9  4@4.com  X        3/2/19
  10 5@5.com  X        4/3/19
  </code></pre>
  
  <p>I want to find the number of unique values in column A where column B == X and column C falls within a particular month (provided by E1:H1).</p>
  
  <p>I'd like to avoid using multiple pivot tables for each date range if possible.</p>
  
  <p>I've tried two formulas which don't work.</p>
  
  <pre><code>{=SUM(IF((B2:B10="X")*(C2:C10&gt;=E$1)*(C2:C10&lt;F$1), 1 / COUNTIFS(B2:B10, "X", C2:C10, "&gt;="&amp;E$1, C2:C10, "&lt;"&amp;F$1, A2:A10, A3:A10)), 0)}
  </code></pre>
  
  <pre><code>=SUMPRODUCT(((B2:B10="X")*(C2:C10&gt;=E$1)*(C2:C10&lt;F$1)) / (COUNTIFS(A:A, A2:A10, B2:B10, "X", C2:C10, "&gt;="&amp;E$1, C2:C10, "&lt;"&amp;F$1)))
  </code></pre>
  
  <p>I've seen similar questions in Stack Overflow but none worked for me.</p>
  
  <p>Any help appreciated.</p>