2 Problems - Sumproduct and maybe a vlookup?

[eblake]

Greetings All,

We get 2 reports from our data base, one shows how many calls each agent gets from each center, and another shows how many cases they worked each day. What we would like to do is combine the cases per day and calls from center 1 and calls from all other centers into a single sheet for each agent by userid.

Example:

Yahoo_Agent_AnD_alpha_2EB Posted.xls
	A	B	C	D	E
2	AgentName	Ansen,Arthur	AA1234
3	date:	Non-Center1Calls	Center1Calls	CasesWorked	DailyTotal
4	8/7/2006	6	5	3	14
Ansen, Arthur

So what I need is a formula that will lookup the value from this worksheet and put it on the agent breakout by agent userid and date:

Agent_AnD_alpha_2EB Posted.xls
	A	B	C	D	E	F
1	AgentName	userid	Manager	Date	CallLocation	TotalofCallLocation
2	Ansen,Arthur	AA1234	Wanson	8/7/2006	Center8	2
3	Ansen,Arthur	AA1234	Wanson	8/7/2006	Center1	5
4	Ansen,Arthur	AA1234	Wanson	8/7/2006	Center5	2
5	Ansen,Arthur	AA1234	Wanson	8/7/2006	Center6	1
6	Ansen,Arthur	AA1234	Wanson	8/7/2006	Center9	1
Count_Calls_all_Q

And another formula to lookup cases worked from this worksheet by userid and date and put it on the agent sheet:

Agent_AnD_alpha_2EB Posted.xls
	A	B	C	D
1	Date	AgentFull	UID	CasesWorked
2	8/1/2006	Ansen,Arthur	AA1234	9
3	8/4/2006	Ansen,Arthur	AA1234	6
4	8/7/2006	Ansen,Arthur	AA1234	3
5	8/8/2006	Ansen,Arthur	AA1234	15
Count_cases_all_Q

We've been playing with Sum(if's and Sumproduct but just cant seem to get it to work. Any help would be appreciated. Sorry for the length of the examples.

Thanks in advance

 

[NBVC]

Try these:


Non-Center1 Calls:

=SUMPRODUCT(--([Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$B$2:$B$1000=$C$2),--([Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$D2:$D$1000=$A4),--([Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$E$2:$E$1000<>"Center1"),[Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$F$2:$F$1000)

Center1 Calls:

=SUMPRODUCT(--([Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$B$2:$B$1000=$C$2),--([Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$D2:$D$1000=$A4),--([Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$E$2:$E$1000="Center1"),[Agent_AnD_alpha_2EBPosted.xls]Count_Calls_all_Q!$F$2:$F$1000)

Cases Worked:

=SUMPRODUCT(--([Agent_AnD_alpha_2EBPosted.xls]Count_cases_all_Q!$C$2:$C$1000=$C$2),--([Agent_AnD_alpha_2EBPosted.xls]Count_cases_all_Q!$A2:$A$1000=$A4),[Agent_AnD_alpha_2EBPosted.xls]Count_cases_all_Q!$D$2:$D$1000)

Adjust ranges to suit.

edit: I made some minor changes....not sure if you figured them out already, but for summing, the last array of each formula should not have had the double unary (--) in front.

 

[eblake]

Thank you, that works perfectly. We knew it was a sumproduct, just couldnt seem to get the syntax correct.