# 2 Problems - Sumproduct and maybe a vlookup?

#### eblake

##### Active Member
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
ABCDE
2AgentNameAnsen,ArthurAA1234
3date:Non-Center1CallsCenter1CallsCasesWorkedDailyTotal
48/7/200665314
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
ABCDEF
1AgentNameuseridManagerDateCallLocationTotalofCallLocation
2Ansen,ArthurAA1234Wanson8/7/2006Center82
3Ansen,ArthurAA1234Wanson8/7/2006Center15
4Ansen,ArthurAA1234Wanson8/7/2006Center52
5Ansen,ArthurAA1234Wanson8/7/2006Center61
6Ansen,ArthurAA1234Wanson8/7/2006Center91
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
ABCD
1DateAgentFullUIDCasesWorked
28/1/2006Ansen,ArthurAA12349
38/4/2006Ansen,ArthurAA12346
48/7/2006Ansen,ArthurAA12343
58/8/2006Ansen,ArthurAA123415
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.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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)

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.

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

Replies
6
Views
437
Replies
4
Views
146
Replies
3
Views
137
Replies
0
Views
64
Replies
21
Views
440

1,203,402
Messages
6,055,185
Members
444,768
Latest member
EMGVT

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

### Which adblocker are you using?

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

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