Sumproduct with multiple criteria in one column

[jackfox68]

I am trying to determine the Average Handling Time for each Rep based on user defined project, start and end date. The project is comprised of splits that are defined in the top right hand corner of the example. The results would be populated in the bottom right hand chart labeled AHT. I have tried using sumproduct with the -- but still can't seem to get it to work using more than one split.

Thanks for any help offered

Brian

	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Agent Name	Split	ACD Calls	AHT		Start Date	End Date	Project		Project	Split
2	6/5/2017	Perez, K CaLL	618	0	0		6/5/2017	6/7/2017	Project 1		Project 1	617,628
3	6/5/2017	Perez, K CaLL	627	0	0						Project 2	400,416
4	6/5/2017	Rodimon, K CaLL	618	15	286.8666687
5	6/5/2017	Rodimon, K CaLL	627	11	187.0909119
6	6/5/2017	Mercado, K CaLL	400	59	219.4576263		CSR Name	AHT
7	6/5/2017	Mercado, K CaLL	416	43	221.162796		Perez, K CaLL	?
8	6/5/2017	Mercado, K CaLL	618	0	0		Rodimon, K CaLL	?
9	6/5/2017	Mercado, K CaLL	627	0	0		Mercado, K CaLL	?
10	6/6/2017	Perez, K CaLL	618	2	251
11	6/6/2017	Perez, K CaLL	627	104	221.509613
12	6/6/2017	Mercado, K CaLL	400	53	240.8301849
13	6/6/2017	Mercado, K CaLL	416	46	220.6087036
14	6/6/2017	Mercado, K CaLL	618	1	723
15	6/6/2017	Mercado, K CaLL	627	0	0
16	6/7/2017	Perez, K CaLL	618	49	249.9183655
17	6/7/2017	Perez, K CaLL	627	55	240.4909058
18	6/7/2017	Rodimon, K CaLL	400	23	309.6956787
19	6/7/2017	Rodimon, K CaLL	416	25	303
20	6/7/2017	Rodimon, K CaLL	618	0	0
21	6/7/2017	Rodimon, K CaLL	627	0	0
22	6/7/2017	Mercado, K CaLL	400	48	265.9791565
23	6/7/2017	Mercado, K CaLL	416	42	238.6666718
24	6/7/2017	Mercado, K CaLL	618	0	0
25	6/7/2017	Mercado, K CaLL	627	0	0
26	6/8/2017	Perez, K CaLL	618	36	247.4166718
27	6/8/2017	Perez, K CaLL	627	66	186.8787842
28	6/8/2017	Rodimon, K CaLL	400	38	198.5263214
29	6/8/2017	Rodimon, K CaLL	416	36	362.3055725
30	6/8/2017	Rodimon, K CaLL	618	0	0
31	6/8/2017	Rodimon, K CaLL	627	0	0
32	6/8/2017	Mercado, K CaLL	400	37	223.0270386
33	6/8/2017	Mercado, K CaLL	416	39	258.4102783
34	6/8/2017	Mercado, K CaLL	618	1	434
35	6/8/2017	Mercado, K CaLL	627	0	0

<tbody>
</tbody>

 

[Marcelo Branco]

Not sure i understand what you are trying to do.
Using your data sample above, see if this does what you need.

Formula in H7 copied down
=SUMPRODUCT(--(B$2:B$35=G7),--ISNUMBER(MATCH("*,"&C$2:C$35&",*",","&L$2:L$3&",",0)),E$2:E$35)

Hope this helps

M.

 

[jackfox68]

Hi Marcelo,

Thank you for your reply, I do appreciate it.

What I am trying to do is find the weighted average AHT for all CSR's based on a dynamic date and project. So the user will choose from a drop down which date range they want to view and for which project. Now the projects are comprised of skills, so I was hoping that when the user selected the project the right skills would be included.

Am I making sense now? It is a bit confusing. Essentially I am running a call center and trying to create a manual scorecard.

Thanks again

Brian

 

[Marcelo Branco]

Still not 100% sure about what you need...

See if this works (assumes drop-downs in G2, H2 and I2)
H7 copied down
=SUMPRODUCT(--(B$2:B$35=G7),--(A$2:A$35>=G$2),--(A$2:A$35<=H$2),--ISNUMBER(SEARCH(","&C$2:C$35&",",","&INDEX(L$2:L$3,MATCH(I$2,K$2:K$3,0))&",")),E$2:E$35)

M.

 

[jackfox68]

I will try it now and will let you know. Here is an example of the formula I am trying to work with. I have the Project Splits manually entered into the array.

'=SUM(SUMPRODUCT(--(DD_CSRName=A2),--(DD_Split={618,627}),--(DD_Date>=L3),--(DD_Date<=L5),DD_ACDCalls,DD_AHT))/SUM(SUMIFS(DD_ACDCalls,DD_Date,">="&L3,DD_Date,"<="&L5,DD_CSRName,"="&A2,DD_Split,"="&{618,627}))

 

[Marcelo Branco]

To understand my formula in post #4

--(B$2:B$35=G7) checks if the names in column B are equal to the name in G7

--(A$2:A$35>=G$2),--(A$2:A$35<=H$2) these two together check if the dates in column A are inside the interval chosen by the user

--ISNUMBER(SEARCH(","&C$2:C$35&",",","&INDEX(L$2:L$3,MATCH(I$2,K$2:K$3,0))&",")) checks if the splits in column C match the splits of the chosen project

M.

 

[jackfox68]

Yes I understand the formula, thank you. Do you think it could be the way I am storing the splits in L2 and L3?

 

[Marcelo Branco]

Yes I understand the formula, thank you. Do you think it could be the way I am storing the splits in L2 and L3?

The formula worked perfectly for me using your data sample above.

M.

 

[jackfox68]

ok - let me spend some time at it then and see if I can get it to work. If it worked for you it has to be something I am doing. I'll keep you posted, and thanks again.

 

[jackfox68]

I think the issue might be with the way I cam storing the splits in cells L2 and L3. When I click on the cell, the formula bar shows them as one big number, so for project 1 it readds 618627 and it shoudl read 618,627.