vlookup, sumif, if, countif, help

[chintu49]

well, this is going to be a very big task for the person who will help me here, but heartfelt thankyou to you if you help me.....

i have a raw data sheet where the column A will have few names suppose they are John, Kelly, Matt, Micheal and Keith (lets call them agents)....Now in column B I have date and time at which case was logged, this cases are logged in 8 hrs of shift by each of them and can be counted by selecting either their names repeated that many times or by counting the column B which has date and time (i.e at wht time on that day they have made how many cases). Now the thing is I want a formula which will look at the name say Kelly and then return the value that between 10 - 11 how many cases were logged. so i have the format say for 8 hours starting 10 - 11, 11 - 12 and so on till 5 - 6 and in every row for this 8 hours it should give me hourly count of number of cases logged.

I hope I have explained it properly. If you need more explanation about my problem then kindly let me know.


Also i have one more issue where i have one column that has half hourly count of number of calls taken, i.e say 7.00 to 7.30, 7.30 to 8.00 and so on, i would like to sum those half hourly values in one hour i.e is 7.00 to 8.00 and looking at the agents unique id, it should return that hourly count. is it possible ?


yours sincerely,

chintu.....

[mangesh_yadav]

Can you provide some sample data?

Mangesh

[chintu49]

well,

column a column b
John 5/18/2005 10:15
John 5/15/2005 10:20
John 5/15/2005 11.30
Kelly 5/15/2005 12:30
kelly 5/15/2005 12:45
kelly 5/15/2005 1:15
kelly 5/15/2005 2:05

Now I want a formula which will look at John or may be i can also insert one more column which will be a unique ID say 1234 for John and then will return the value that between 10 - 11 he made two cases.

[mangesh_yadav]

This will give you John for 10-11

=SUMPRODUCT(--(A1:A7="John"),--(HOUR(B1:B7)>=10),--(HOUR(B1:B7)<11))

for your example

- Mangesh

[chintu49]

Hey Mangesh,

Thanx a lot, it works...also if you can address to one more issue of mine that was there in the first posting.

i have one column that has half hourly count of number of calls taken, i.e say 7.00 to 7.30, 7.30 to 8.00 and so on, i would like to sum those half hourly values in one hour i.e is 7.00 to 8.00 and looking at the agents unique id, it should return that hourly count. is it possible ?

I will give you an example also....

Column A Column B Column C Column D
7:00 7:30AM 6 a1055
7:30 8:00AM 5
8:00 8:30AM 4
8:30 9:00AM 3
9:00 9:30AM 2
9:30 10:00AM 3

Now wht I want is that Column C is the no of calls taken in half hour, i want a formula that will sum the two half hours say from 7.00 to 7.30 and 7.30 to 8.00 and give me hour count and then return the value by looking at the Column D which will be a unique ID. so in this case when it looks at the unique ID a1055 and return the total hourly count as 11 between 7 - 8 AM.

thnx

chintu

[mangesh_yadav]

Try this:

=SUMPRODUCT(--(D1:D6="a1055"),--(HOUR(A1:A6)>=7),--(HOUR(A1:A6)<8),C1:C6)

used your data as example.

Mangesh

[chintu49]

Hi Mangesh,

The formula is not working for me, ok i will explain u properly in a different form.

Time ACD Calls
Name Totals 6
a1055 7:00 7:30 AM 0
7:30 8:00AM 0
8:00 8:30AM 0
8:30 9:00AM 0


Column A will have Agent ID, Column B and C will have Timings say 7:00 to 7:30 and so on (hourly count) and Column D will have no of Calls taken. Now I want to add the count of calls between 7 to 8, 8 to 9 and so on. Now the trick is that whenever i write a formula it should look at the column A having unique ID and then return the total of count in one hour in the column E. How is it possible, if you have not understood then i shall explain you again