title: How can I replicate excel COUNTIFS in python/pandas?
body: <p>I would like to get a count for the # of the previous 5 values in df['A'] which are &lt; current value in df['A'] &amp; are also >= df2['A']. I am trying to avoid looping over every row and columns because I'd like to apply this to a larger data set.</p>
  
  <p>Given this...</p>
  
  <pre><code>list1 = [[21,101],[22,110],[25,113],[24,112],[21,109],[28,108],[30,102],[26,106],[25,111],[24,110]]
  df = pd.DataFrame(list1,index=pd.date_range('2000-1-1',periods=10, freq='D'), columns=list('AB'))
  df2 = pd.DataFrame(df * (1-.05))
  </code></pre>
  
  <p>I would like to return this (solved in Excel with COUNTIFS)...</p>
  
  <p><a href="https://i.stack.imgur.com/vM0Fn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vM0Fn.png" alt="enter image description here"></a></p>
  
  <p>The line below achieves the first part (thanks Alexander), and Divakar and DSM have also weighed in previously (<a href="https://stackoverflow.com/questions/33289664/comparing-single-dataframe-value-to-previous-10-in-same-column">here</a> and <a href="https://stackoverflow.com/questions/28506255/in-pandas-how-to-calculate-countif-on-a-moving-window-basis">here</a>).</p>
  
  <pre><code>df3 = pd.DataFrame(df.rolling(center=False,window=6).apply(lambda rollwin: sum((rollwin[:-1] &lt; rollwin[-1]))))
  </code></pre>
  
  <p>But I am unable to to add the comparison to df2. Please help. </p>
  
  <p>FOLLOW UP on 10/27/16:</p>
  
  <p>How would I write the lambda above as a standard function? </p>
  
  <p>10/28/16: </p>
  
  <p>See below, taking col 'A' from both df and df2, I am trying to count how many of the previous 5 values from df['A'] fall between the current df2['A'] and df['A']. Said differently, how many from each orange box fall between the yellow low-high range?</p>
  
  <p><a href="https://i.stack.imgur.com/jSmk0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jSmk0.png" alt="enter image description here"></a></p>
  
  <p>UPDATE: different list1 data produces incorrect df3...</p>
  
  <pre><code>list1 = [[21,101],[22,110],[25,113],[24,112],[21,109],[26,108],[25,102],[26,106],[25,111],[22,110]]
  df = pd.DataFrame(list1,index=pd.date_range('2000-1-1',periods=10, freq='D'), columns=list('AB'))
  df2 = pd.DataFrame(df * (1-.05))
  
  df3 = pd.DataFrame(
       df.rolling(center=False,window=6).apply(
            lambda rollwin: pd.Series(rollwin[:-1]).between(rollwin[-1]*0.95,rollwin[-1]).sum()))
  
  df
  Out[9]: 
               A    B
  2000-01-01  21  101
  2000-01-02  22  110
  2000-01-03  25  113
  2000-01-04  24  112
  2000-01-05  21  109
  2000-01-06  26  108
  2000-01-07  25  102
  2000-01-08  26  106
  2000-01-09  25  111
  2000-01-10  22  110
  
  
  df3
  Out[8]: 
                A    B
  2000-01-01  NaN  NaN
  2000-01-02  NaN  NaN
  2000-01-03  NaN  NaN
  2000-01-04  NaN  NaN
  2000-01-05  NaN  NaN
  2000-01-06  1.0  0.0
  2000-01-07  2.0  0.0
  2000-01-08  3.0  1.0
  2000-01-09  2.0  3.0
  2000-01-10  1.0  3.0
  </code></pre>
  
  <p>EXCEL EXAMPLES (11/14): see below, trying to count how many numbers in the blue box fall between the range highlighted in orange. </p>
  
  <p><a href="https://i.stack.imgur.com/vBmcg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vBmcg.png" alt="enter image description here"></a></p>