SUM data according to dates in Excel

Question

Hi,

My problem is the following...

I have a little aeroplane and I need to track the hours. I have to track the hours by sectors and not the total of the day (that's why sometimes I have 2 or 3 on the same day).

Now this is the problem... On column C I need to SUM the hours of the last 7 days. And any given 7 days, not just last week. To do it manually is quite easy... the problem is that I need a formula as my records are quite large...

Here with a small example (let's say that there was NO HOURS before 15/01/2009)...

COLUMN A-------COLUMN B-------COLUMN C

DATE--------------HOURS-------HOURS LAST 7 DAYS

15/01/2009-------01:00-------01:00

15/01/2009-------02:15-------03:15

16/01/2009-------01:15-------04:30

17/01/2009-------01:30-------06:00

18/01/2009-------01:30-------07:30

18/01/2009-------01:00-------08:30

18/01/2009-------02:00-------10:30

19/01/2009-------02:30-------13:00

19/01/2009-------03:00-------16:00

20/01/2009-------////////--------16:00

21/01/2009-------01:00-------17:00

22/01/2009-------01:30-------15:15

23/01/2009-------02:00-------16:00

I've been fighting for the last weeks trying to figure out a formula but no luck... any suggestions?

Thanks

Answer 1

Here's the data in better format if someone wants to try:

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)

This was based on Sum of named ranges conditional to date?. The idea is to first compute the total sum: SUM($B$1:$B$13)

then subtract any values that happened older than 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 point is to use SUMIF function, which "adds the cells specified by a given criteria."

Answer 2

First thing is to get the begin date, which would be the following function:

=Now() - 7

If you renamed that cell to "WeekBegin", then you could use the following formula to calculate the total hours:

=SUMIF(A:A,">=" & WeekBegin,B:B)

Notice that I used column references; this was to both simplify the formula, but also allow you to add new data to the end of the range easily. You will need to take care that your WeekBegin cell is not in that column A or column B, otherwise you'll get a circular reference warning.

If you planned to have numeric data above or below your input range, you would need to explicitly call out the sum and criteria ranges as follows:

=SUMIF(A2:A14,">=" & WeekBegin,B2:B14)

Additionally, you may find that your result comes up initially as a decimal. That's Excel's date serial format, so you may need to format your result as time.

Hope that helps!

[Edit: On second pass, if you're looking to sum a range based on a from and to date (so any 7 days as you seem to imply in your post), look for the previous poster's note, i.e.:

=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 the standard SUMIF.

Happy Summing!]

Answer 3

Another solution that basically does much the same as the earlier offered 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're not familiar with array formulas, the {} outer brackets just 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 hit Ctrl+Shift+Enter to indicate that it's an array formula.