title: Python/Pandas: Complicated Excel SUMIFS across dataframes
body: <p>I'm working with two dataframes, One called Sales, pictured here
  <a href="https://i.stack.imgur.com/kORFg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kORFg.png" alt="enter image description here"></a></p>
  
  <p>And another dataframe, called Claims, which is pictured here
  <a href="https://i.stack.imgur.com/sVgUa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sVgUa.png" alt="enter image description here"></a></p>
  
  <p>I would like to create a new column called "SUMIFS" in another dataframe entitled "Subset" (i.e. Subset['SUMIFS'] = ...), and I am wondering if it is possible to perform an excel like SUMIFS  operation across dataframes in pandas. The criteria for the SUMIFS would be:</p>
  
  <p><strong>sumrange --</strong> Sales['Vehicle Count']</p>
  
  <p><strong>Criteria 1 --</strong> Sales['Vehicle Production Month/Year'] = Claims['Vehicle Production Month/Year']</p>
  
  <p><strong>Criteria 2 --</strong> Sales['DIS since RUN DATE'] &lt; Claims['Claim DIS']</p>
  
  <p>Here is the Sales Dataframe in spreadsheet form</p>
  
  <p><a href="https://i.stack.imgur.com/ex3vY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ex3vY.png" alt="enter image description here"></a></p>
  
  <p>Here is the Claims Dataframe in spreadsheet form</p>
  
  <p><a href="https://i.stack.imgur.com/aWiQP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aWiQP.png" alt="enter image description here"></a></p>
  
  <p>And, Finally, here is the original formula that I'm using in excel, that I would like to convert to Python: =SUMIFS(Sales!$D$2:$D$1156, Sales!$A$2:$A$1156, Claims!B2, Sales!$E$2:$E$1156, &lt;"&amp;Claims!O2)                                                                        </p>