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]

Actually, I don't see any difference in the results with either formula.

Your formula with a different separators looks good; I just would use a different separator, maybe even a space... though some people use space as thousand separator, too. So i would stick with dash just to be sure.

Yes, the formula with proper separators (splits on a single cell) also works.
But the formula above (post #19) uses the setup provided in post #13 - splits in different cells (range M2:S6)

M.

 

[jackfox68]

So I was able to get the formula to work and sum up the AHT for both splits for each CSR for a defined Date Range, I changed the comma for a plus sign, not sure why the comma didn't work. Now what I need to do it weight the AHT with the number of calls so I can come up with a weighted average. So normally when I do this with a static data set for a single criteria I would use a formula that would look something like this.

=sumproduct(ACDCalls:ACDCalls,AHT:AHT)/sum(ACDCalls)

This would provide me with a weighted average. I don't think either of the solutions provide a weighted average, it just appears to sum up the total AHT time. Can either of the solutions provided be tweaked so they would provide a weighted average?

I am going to tinker around with both formula to see what I can come up with in the meantime.

I can't express how much I appreciate your help, I am that much closer to a solution, and continuing to learn.

 

[jackfox68]

This is what I was trying but no luck.

=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,$D$2:$D$35)/SUMIFS($D:$D,$B:$B,$G7,$A:$A,">="&G2,$A:$A,"<="&H2,ISNUMBER(SEARCH(("+"&DD_Split&"+","+"&INDEX(($L$2:$L$3,MATCH(I$2,K$2:K$3,0))&"+")))))

 

[jackfox68]

Holy Sheet1 I think I was able to piece together something that works. I have tried it on a couple of smaller samples with success, now I will apply it to a much larger data set. I will keep you posted on whether or not it works! Thank you so much. Here is what I used:

=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,$D$2:$D$35)/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))&"+")),$D$2:$D$35)

 

[jackfox68]

oh one last question, at least for now. Can I replace the defined ranges, e.g A2:A35 with dynamic ranges? Would the formula still work?

 

[jackfox68]

oh one last question, at least for now. Can I replace the defined ranges, e.g A2:A35 with dynamic ranges? Would the formula still work?

I just updated the formula using the named ranges and it worked just fine.