SUM data according to dates in Excel
My problem is this ...
I have a small plane and I need to keep track of the watch. I have to keep track of hours by sector, not the sum of the day (why sometimes I have 2 or 3 on the same day).
Now this is a problem ... On column C, I need the SUM of the hours of the last 7 days. And any 7 days, not just last week. Doing it manually is pretty easy ... the problem is I need a formula since my entries are quite large ...
Here with a small example (say NO HOURS until 01/15/2009) ...
COLUMN A ------- COLUMN B ------- COLUMN C
DATE -------------- HOURS ------- HOURS LAST 7 DAYS
01/15/2009 ------- ------- 1:00 1:00
01/15/2009 ------- ------- 2:15 3:15
01/16/2009 ------- ------- 1:15 4:30
17/01/2009 ------- ------- 1:30 6:00
01/18/2009 ------- ------- 1:30 7:30
18/01/2009 ------- ------- 1:00 8:30
18/01/2009 ------- ------- 2:00 10:30
19/01/2009 ------- ------- 2:30 13:00
19/01/2009 ------- ------- 3:00 16:00
20/01/2009 ------- //////// -------- 16:00
01/21/2009 ------- ------- 1:00 17:00
01/22/2009 ------- ------- 1:30 15:15
23/01/2009 ------- ------- 2:00 16:00
I've struggled over the past weeks trying to figure out the formula, but no luck ... any suggestions?
thanks
First of all, you need to get the start date, which will be the following function:
=Now() - 7
If you renamed this cell to "WeekBegin", you can use the following formula to calculate the total number of hours:
=SUMIF(A:A,">=" & WeekBegin,B:B)
Note that I used column references; it was both a simplification of the formula and made it easy to add new data to the end of the range. You will need to take care that your WeekBegin cell is not in that Column A or Column B, otherwise you will get a circular reference warning.
If you plan on having numeric data above or below your input range, you will need to explicitly call the ranges of sums and criteria as follows:
=SUMIF(A2:A14,">=" & WeekBegin,B2:B14)
Also, you may find that your result appears as decimal first. This date format is in Excel format, so you may need to format the result as time.
Hope it helps!
[Edit: on the second pass, if you want to sum a range based on from and to date (so every 7 days as you seem to imply in your post), find the previous poster note, that is:
=SUM(B:B) - SUMIF(A:A, "<="& BeginDate, B:B) - SUMIF(A:A, ">"& EndDate, B:B)
A more elegant solution is offered in Excel 2007 using the SumIFS () function:
=SUMIFS(B:B, A:A, ">=" & FromDate,A:A, "<" & ToDate)
Note that the arguments for SUMIFS are in a different order than standard SUMIF.
Happy summation!]
source to share
Another solution that basically does the same as the previously suggested solutions:
In C1, enter the following formula:
{=SUM(IF(($A$1:$A1>=($A1-6))*($A$1:$A1<=$A1), $B$1:$B1, 0))}
And then just drag the formula down.
If you are not familiar with array formulas, the outer parentheses {} simply indicate that the formula is an array formula. To get it to execute correctly, you need to copy the part inside the brackets {} into the formula bar and then press Ctrl + Shift + Enter to indicate that this is an array formula.
source to share
Here's the data in its best format if anyone wants to try it:
15-Jan-2009 01:00
15-Jan-2009 02:15
16-Jan-2009 01:15
17-Jan-2009 01:30
18-Jan-2009 01:30
18-Jan-2009 01:10
18-Jan-2009 02:00
19-Jan-2009 02:30
19-Jan-2009 03:00
20-Jan-2009
21-Jan-2009 01:00
22-Jan-2009 01:30
23-Jan-2009 02:00
I got the function:
=SUM($B$1:$B$13)-SUMIF($A$1:$A$13, "<="& (A1- 7), $B$1:$B$13) - SUMIF($A$1:$A$13, ">"& (A1), $B$1:$B$13)
Was it based on the Sum of the named ranges conventionally up to date? ... The idea is to first calculate the total:SUM($B$1:$B$13)
then subtract any values that happened 7 days ago: SUMIF($A$1:$A$13, "<="& (A1- 7), $B$1:$B$13)
then subtract any values that happened in the future: SUMIF($A$1:$A$13, ">"& (A1), $B$1:$B$13)
The goal is to use the SUMIF function , which "adds cells specified by a given criterion".
source to share