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.
column A column B
06 JAN 12 0hours
07 JAN 12 1hours
08 JAN 12 1hours
09 JAN 12 1hours
10 JAN 12 0hours
11 JAN 12 0hours
12 JAN 12 1hours
TODAY 1hours
13 JAN 12 1hours
14 JAN 12 1hours
Anyone can help will be appreciated -thanks
p.s I amended the above data for better clarification
DATE DAILY HOURS TOTAL HRS FLOWN IN LAST 7 DAYS
A B C
6/Jan/12 1.0 8.0
7/Jan/12 1.0
8/Jan/12 1.0
9/Jan/12 1.0
10/Jan/12 1.0
11/Jan/12 1.0
12/Jan/12 1.0
13/Jan/12 1.0
14/Jan/12 1.0
15/Jan/12 1.0
16/Jan/12 1.0
17/Jan/12
18/Jan/12
19/Jan/12
20/Jan/12
21/Jan/12
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,">="&TODAY()-7,B:B,"<="&TODAY()).
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".
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))
Best Answer
Try this: