Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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):

SU502292 example

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:

Second SU502292 example

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.