Need help with COUNTIF or similar formula with multiple criteria

[ivand84]

Hi...

I am a novice Excel user and I work at a call center where we're trying to set up a few reports for a small team of agents that log all their calls in Excel. I need a report that will break down and automatically count the number of calls for each hour of the day based on how each call was dispositioned. However even though there are about 10 ways to disposition the calls, I will only need to break it down into 2 main categories, so each hour of each day will need 2 separate columns and 2 separate formulas.

So the formula will be based on a range of 6 columns in the worksheet where our agents log the calls. The first criteria will be the date, the 2nd criteria will be the hour of the day, and then the 3rd criteria will be based on the disposition of the call. So for example, for September 27th, I need 2 separate columns - one that logs the first specific category of calls that only took place between 8AM to 8:59AM for that date, and then another one which reads the other type of calls for the same date and hour.

I'm not sure if COUNTIF can do this. Can I set up multiple criteria with COUNTIF?

An example of the data, which hopefully makes this easier to understand:

On the worksheet where agents log the calls, here is one specific call on the first row below the headers:

A2 = 9/27/12 (the date of the call)
B2 = 8:15 AM
C2 = the phone number, not necessary for the formula, obviously.
D2 = the name of the customer.
E2 = notes, also not necessary.
F2 = CC (the code for how the call was dispositioned).

So on the separate worksheet, I need 2 separate formulas for September 27th. Both columns will look for calls only made on the 27th from 8AM to 8:59AM that were dispositioned with a variety of different codes on the 6th column. There is a set of about 3 or 4 codes that combine for the first category of calls (let's call those codes NI, NA or $$), and then the other column does the same thing for the same date and hour for a separate set of codes (like CC, 30sec and NQ).

Hopefully this makes it clear. I would imagine this is definitely doable, and any help would be greatly appreciated! I'd love to have this done tomorrow.

Thank you!

Ivan

[alansidman]

Suggest you try Counifs function

Look here

http://office.microsoft.com/en-us/ex...010047494.aspx

[ivand84]

OK wow, I can't believe I didn't see that function, I should be able to figure that out and that should help me automate a bunch of reports that I have to do. Thanks a lot Alan!

[vlady]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.