# Use countif for a weekly call log

#### rdunt

##### New Member
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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### iliace

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

#### lenze

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

lenze

#### rdunt

##### New Member
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

##### Well-known Member
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

##### New Member
Here's how I have it setup. What will the formula be for the business hours field?
Help Desk Voice Mail Log.xlsx
ABCD
3During
56/2/20081
66/9/20081
76/16/20080
86/23/2008
96/30/2008
107/7/2008
117/14/2008
127/21/2008
137/28/2008
Summary

#### iliace

##### Well-known Member
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.

perfect. Thanks.

Replies
1
Views
162
Replies
2
Views
106
Replies
2
Views
353
Replies
6
Views
274
Replies
3
Views
138

1,191,272
Messages
5,985,684
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back