Use countif for a weekly call log

[rdunt]

I'm trying to setup a couple formulas to determine the number of calls that go to voice mail every week. Then if there are any calls that went to voice mail I want to be able to have a formula to determine if is was during business hours.

Part 1: I have a call log tab setup. I'm manually entering the calls that go to voice mail in this tab. Column A is the date (6/2/2008), Column B is time (6:50 AM). The formula I've come up with to calculate the number of calls each week is: =COUNTIFS('Call Log'!A4:A128,">=6/2/2008",A4:A128,"<=6/6/2008"). The problem with this formula is that when I copy it down the date range for the current week doesn't change. Is there anyway for the date to automatically change?

Part 2: After the first formula determines if there were any calls that made it to voice mail. I want to be able to tell if it was during business hours (AM-PM CST). Any idea how I can do that?

Thanks.

 

[iliace]

You can do this all with a single COUNTIFS. How do you determine what the "current week" is?

 

[lenze]

You could just use a Pivot Table with dates group by Days(7) and Times grouped as needed

lenze

 

[rdunt]

The current week is coming from the summary tab.

A4: For the week of
A5: 6/2/2008
A6:6/9/2008
A7: 6/16/2008
and so on.

 

[iliace]

Try something like this, on your summary tab. I'm assuming column B are actual time values for the calls.

=COUNTIFS('Call Log'!A4:A128,">="&A5, 'Call Log'!A4:A128,"<="&A6, 'Call Log'!B4:B128,">="&("9:00 AM"+0), 'Call Log'!B4:B128,"<="&("5:00 PM"+0))

 

[rdunt]

Here's how I have it setup. What will the formula be for the business hours field?

Help Desk Voice Mail Log.xlsx
	A	B	C	D
3			During
4	FortheWeekof	Total	BusinessHours
5	6/2/2008	1
6	6/9/2008	1
7	6/16/2008	0
8	6/23/2008
9	6/30/2008
10	7/7/2008
11	7/14/2008
12	7/21/2008
13	7/28/2008
Summary

 

[iliace]

To automate date entry:

=COUNTIFS('Call Log'!A4:A128,">="&A5,A4:A128,"<="&(A6-3))

and copy down. Assuming your business hours are 9-5, same formula as I listed above should work:

=COUNTIFS('Call Log'!A4:A128,">="&A5, 'Call Log'!A4:A128,"<="&(A6-3), 'Call Log'!B4:B128,">="&("9:00 AM"+0), 'Call Log'!B4:B128,"<="&("5:00 PM"+0))

and copy down.

 

[rdunt]

perfect. Thanks.