formula to count a range of times in reference to a particular name

[tonyjb1982]

Hi everyone,

I am creating a document with a range of times in one colums (0600-0800, 0801-0900 and so on). In another column i have a list of customers in a drop down menu. I need to be able to create (on another worksheet) a chart that counts how many instances that particular customer name arises but between the specific times set.

So for instance, customer a 0600-0700 arrives 5 times, between 0701-0800 6 times and so on. I have about 8 different customers to set this for.

I have no idea where to start with the formula, please help?

 

[pbornemeier]

With input data: Customer Names in column A and arrival times in column B on Sheet1 (assume data in rows 2 to 13 for example)
Set up the output table on sheet 2L
A2 = "Name"
B1 = "Start Time"
B2 = "End Time"

start time for first period in C1 and on to the right for subsesquent periods
finish time for first period in C2 and on to the right for subsesquent periods
First Customer name in A3 and fill down with all customers
Formula in C3 to be dragged down and across to fill the table:

=SUMPRODUCT(--($A3=Sheet1!$A$2:$A$13),--(Sheet2!C$1<=Sheet1!$B$2:$B$13),--(Sheet2!C$2>=Sheet1!$B$2:$B$13))

Ensure that times are numbers and represented the same way in both worksheets. Either as fractions of a day (most correct) or integer values (ugly & somewhat wrong, but will work).

If you need to have your output headers in the form of "0701-0800" then you will have to use the appropriate string trimming and conversion functions in the above formula to make it work.