title: EXCEL formula calculating the sum of a column for the last 7 days from today as a reference?
body: <p>Have searched the database but need to specifically sum(of hours flown or days off)in a column for the last 7 days using today as a reference i.e. </p>
  
  <p>column A      column B</p>
  
  <p>06 JAN 12          0hours</p>
  
  <p>07 JAN 12          1hours</p>
  
  <p>08 JAN 12          1hours</p>
  
  <p>09 JAN 12          1hours</p>
  
  <p>10 JAN 12          0hours</p>
  
  <p>11 JAN 12          0hours</p>
  
  <p>12 JAN 12          1hours</p>
  
  <p>TODAY              1hours </p>
  
  <p>13 JAN 12          1hours</p>
  
  <p>14 JAN 12          1hours</p>
  
  <p>Anyone can help will be appreciated -thanks</p>
  
  <p>p.s I amended the above data for better clarification</p>
  
  <p>DATE    DAILY HOURS TOTAL HRS FLOWN IN LAST 7 DAYS</p>
  
  <p>A               B        C</p>
  
  <p>6/Jan/12    1.0 8.0</p>
  
  <p>7/Jan/12    1.0</p>
  
  <p>8/Jan/12    1.0</p>
  
  <p>9/Jan/12    1.0</p>
  
  <p>10/Jan/12   1.0</p>
  
  <p>11/Jan/12   1.0</p>
  
  <p>12/Jan/12   1.0</p>
  
  <p>13/Jan/12   1.0</p>
  
  <p>14/Jan/12   1.0</p>
  
  <p>15/Jan/12   1.0</p>
  
  <p>16/Jan/12   1.0</p>
  
  <p>17/Jan/12</p>
  
  <p>18/Jan/12</p>
  
  <p>19/Jan/12</p>
  
  <p>20/Jan/12</p>
  
  <p>21/Jan/12       </p>
  
  <p>Hi Robert and Barry, thanks for your responses and patience to this 'newbie' in Excel.I have copied the latest format for your consideration. Currently the date column(A) is conditionally formatted to be highlighted when it is "today".The daily hours column(B) is formatted for # and the formula for the Total for last 7days(column C) is =SUMIFS(B:B,A:A,">="&amp;TODAY()-7,B:B,"&lt;="&amp;TODAY()).</p>
  
  <p>Currently the formula calculates the hours from today(C12:C5),and funny if I add input after the todays date it adds it the total too!!I basically need it to calculate the hours flown for the last 7 days not including any hours flown for "today".</p>
  
  <p>Other formulas I have tried are:
  =SUMPRODUCT(--($A$12:$A$64>$M$9),$C$12:$C$64) or
  =SUM(INDEX(B:B,MATCH(99^99,B:B)):INDEX(B:B,MATCH(99^99,B:B)-6))</p>