Time range criteria for Excel

For my data, my timing is actually on a hourly basis,

``````   Date       Time         Sales
1/1/2012   09:00-09:59  15.77
1/1/2012   10:00-10:59  66.13
1/1/2012   11:00-11:59  144.30
1/1/2012   12:00-12:59  128.80

2/1/2012   09:00-09:59  0.00
2/1/2012   10:00-10:59  0.00
2/1/2012   11:00-11:59  25.13
2/1/2012   12:00-12:59  10.01
``````

But now, I want it to be based on a two hourly basis, and to calculate total sales made on the 2 days per 2 hour shift

`````` Time         Sales
09:00-10:59   ?
10:00-12:59   ?
``````

How can I do that in Excel 2007?

-

A pivot table seems a good idea because of the versatility it allows - no real problem with combining more than two days or more than two shifts etc. I'd suggest LOOKUP to add a shift designator 'helper column' and a pivot table as below (where this is filtered for the two days 1/1/12 and 2/1/12):

But there are probably many other ways of achieving your result so your best bet may depend upon what other analysis you might want in future, how much data you have and so forth.

A formula only (not pivot table) solution, without a 'helper column' could be as below, with `G9` copied across to `H9`:

where to maintain at least some flexibility the criteria are referenced rather than hard coded.

SUMIFS in `G9` here adds up Sales where Date is greater than or equal to 01/01/2012 and less than or equal to 02/01/2012 and Time is greater than or equal to 09:00-09:59 and less than or equal to 10:00-10:59.

I suggest a fairly convincing argument in favour of the pivot table approach!

-