title: Excel SUMIF variable date range
body: <p>"main" is a worksheet with a "Week ending" column in it.
  "expenses" is a worksheet with expense figure in column B and specific dates in column A.</p>
  
  <p>I want to sum the expenses that occur on or up to 7 days before the main Week ending date.</p>
  
  <p>Following the examples here: <a href="https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/" rel="nofollow">https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/</a></p>
  
  <p>I came up with this</p>
  
  <pre><code>=SUMIF(expenses!A2:expenses!A1024,main!$A$2&amp;"&lt;="&amp;expenses!$A$2,expenses!B2:expenses!B1024)
  </code></pre>
  
  <p>But it gives me zero.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>The worksheets look like this:</p>
  
  <h2>Main</h2>
  
  <pre><code>|A          |B    |C    |D    |E    |F        |
  |Week Ending|     |     |     |     |Expenses |
  |16/11/2014 |     |     |     |     |formula goes here = should total 100|
  |23/11/2014 |     |     |     |     |formula goes here = should total 25|
  </code></pre>
  
  <h2>Expenses</h2>
  
  <pre><code>|A          |B    |
  |Date       |Value|
  |10/11/2014 |5.00|
  |11/11/2014 |20.00|
  |12/11/2014 |15.00|
  |12/11/2014 |10.00|
  |10/11/2014 |50.00|
  |17/11/2014 |5.00|
  |18/11/2014 |20.00|
  </code></pre>
  
  <p>This works if I just put the week ending date in the expenses sheet:</p>
  
  <pre><code>=SUMIF(expenses!A2:expenses!A1024, main!a2, expenses!b2:expenses!b1024)
  </code></pre>
  
  <p>But I can't change the dates because they have to match the receipts.  So I tried using AND:</p>
  
  <pre><code>=SUMIF(expenses!A2:A1024, AND("&lt;="&amp;main!A2, "&gt;"&amp;main!A2-7), expenses!A2:expenses!A1024)
  </code></pre>
  
  <p>Still zero.  </p>
  
  <p><strong>@pnuts</strong></p>
  
  <p>I tried your SUMIFS suggestion but I still get zero.</p>