Formula to count occurances in a given date range

[thaithco]

I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but can't seem to get any of them to work for me. Here is the graph I am trying to populate:


Date Hours
12a-4a 4a-8a 8a-12p 12p-4p 4p-8p 8p-12a
12-7-09
12-8-09
12-9-09
12-10-09
12-11-09

Any help will save me my job...just kidding but it would be greatly appreciated.

 

[asad]

How about

=sumproduct(--(A:A=X),--(B:B>Y),--(B:B<Z))

 

[thaithco]

I just tried to enter:
=sumproduct(--(A:A=12/7/09),--(B:B>12:00 am),--(B:B<4:00am))

But it is saying that my formula contains an error. I am using 2007 excel.

 

[thaithco]

Here is my data and below is the table I am trying to populate:
Sheet 1
Date Time of call
12/7/09 12:07:00 AM
12/7/09 12:10:00 AM
12/7/09 12:11:00 AM
12/7/09 12:16:00 AM
12/7/09 12:17:00 AM
12/7/09 12:26:00 AM
12/7/09 12:29:00 AM
12/7/09 12:29:00 AM
12/7/09 12:42:00 AM
12/7/09 12:48:00 AM
12/7/09 12:51:00 AM
12/7/09 12:52:00 AM

Sheet 2
Date..............12-4a 4-8a 8-12p. 12p-4p 4p-8p. 8p-12a
12/7/2009
12/8/2009
12/9/2009
12/10/2009
12/11/2009
12/12/2009

 

[asad]

I just tried to enter:
=sumproduct(--(A:A=12/7/09),--(B:B>12:00 am),--(B:B<4:00am))

But it is saying that my formula contains an error. I am using 2007 excel.

You have to have the date and two times in cells and use reference to those cells. Foe Example, you could have the desired date in C1 and Start time in C2 and End time in C3. then the formula will become =sumproduct(--(A:A=$C$1),--(B:B>$C$2),--(B:B<$C$3))

 

[jeffreybrown]

Besides what asad has offered this could work also...

I have included both suggestions...

Excel Workbook
	A	B	C	D	E	F
1	12/7/2009	0:40	4	7-Dec-09	0:00	4:00
2	12/7/2009	1:40	4
3	12/7/2009	2:40
4	12/7/2009	3:40
Sheet4

 

[thaithco]

I am getting confusing results on the formulas provided:

=SUMPRODUCT(--(A:A=$D$1),--(B:B>$E$1),--(B:B<$F$1)) This is giving an outcome of 0

=SUMPRODUCT(--(A:A=DATE(2009,12,7)),--(B:B>TIMEVALUE("12:00:00 AM")),--(B:B<("4:00:00 AM"))) This is giving an outcome of 260

The actual hand count is only 28...not sure how it is coming up with 260. In layman's terms this is what I am needing to know:

How many calls on a particular date between the hours of 12a and 4a did we recieve.

My table is setup as follows:
12/7/09 12:07:00 AM 12/7/09 12:00 AM 4:00 AM
12/7/09 12:10:00 AM 0
12/7/09 12:11:00 AM 260
12/7/09 12:16:00 AM
12/7/09 12:17:00 AM
12/7/09 12:26:00 AM
12/7/09 12:29:00 AM
12/7/09 12:29:00 AM
12/7/09 12:42:00 AM
12/7/09 12:48:00 AM
12/7/09 12:51:00 AM

 

[Aladin Akyurek]

Is 12/7/09 12:07:00 AM in one cell or two?

 

[thaithco]

Yes the date and time are in separate columns. Any help would be appreciated.

 

[Jonmo1]

One or more of your set's of Dates and Times are probably not actually dates or times. They are TEXT strings that look like a date or time.

Use ISNUMBER to find the culprait

=ISNUMBER(A1)

Use that on ALL dates / times involved in the formula. Col A, Col B, D1, E1 and F1

If isnumber returns TRUE, then it is a Date or Time
If isnumber returns FALSE, it is a text string that looks like a date or time.


Also, possibly your TIME in Column B is actually DATE AND TIME, not just time..
format the Time cells as NUMBER, with at least 2 decimals. Are they whole numbers with decimal like 3042.23 or something like that? or are they 0 with decimal 0.23 ?