title: COUNTIFS with range criteria on different sheets
body: <p>I have not read if there is a limitation with COUNTIFS function, but I am experiencing the following:</p>
  
  <p>I want to count the number the number of times two conditions are true in a row. For example</p>
  
  <pre><code>Sheet 1
  USERID    DATE
  SAM        12/1/2014
  SAM        12/3/2014
  SAM        12/4/2014
  JON        12/3/2014
  BOB        11/5/2014
  
  Sheet 2
  Dates to match against {12/3/2014, 12/4/2014, 12/5/2014}
  
  Sheet 3
  USERID   DATECount
  SAM       2
  JON       1
  BOB       0
  </code></pre>
  
  <p>If all this information is in the same sheet the following formula works (Assuming data in Sheet 1 is in columns A and B, and the data in Sheet 2 is in the first row D to F, and the data in Sheet 3 starts at D5.</p>
  
  <pre><code>=COUNTIFS(A:A,D5,B:B,"="&amp;$D$1:$F$1)
  </code></pre>
  
  <p>Currently, to get over this, I have a SUM function and a COUNTIFS for each criteria in the range for criteria 2. But it's pretty ugly since there are 20 criteria. </p>
  
  <p>What I trying to find is a more elegant way to do this, or if there is another function that can return the same results. </p>
  
  <p>Note that the date range can change every month.</p>