Sumproduct Error When calculating Results of a Time Duration

[shamitb]

Hi,


I am facing issues with the Sumproduct formula. I have an array of customer service data of few months. I am trying to calculate the handle time using the Sumproduct formula.


Here is the link to the file - http://sdrv.ms/11YJdhw

File name Raw Data + Calcs v.1


I have two data tables 1 in the login data and second in the Chat Data.


The calcs sheet is where I have problem. Check cell R17 and the formula used there. Somehow it is not giving the results. Initially I didn't have the issue but when I added more data in the Chat Data sheet. I encountered this error.


Please see if someone can help.

 

[Luke M]

This would be a lot easier if you posted the formula you are using rather than us trying to read minds (which is hard to do across long distances). =)

Most likely it's just a syntax error.

 

[shamitb]

Hi Luke M,


I have edited the post and shared the file too. I am not an excel master. See if you can help on this. I think it's a syntax error but not sure.


Thanks,

Shamit

 

[Luke M]

I'm afraid I can't access files from my location, so I'll take a guess. Syntax should look something like:

=SUMPRODUCT((NameField=Name)*(TEXT(DateField,"mmyyyy")=TEXT(CriteriaDate,"mmyyyy"))*(ProductField=Product))


In this example, looking for a count of items where we have a certain name, a certain product, and the date is a certain month of a certain year.

 

[SirJB7]

@Luke M


Hi!


Once again your CASFFML issue, but don't worry, here're a few lines:

-----

[pre]

Month	Weekday	Date	All Offered	Abandoned	Handled	ICCR	ABA	SPL	ABA	Abhishek   (es.abtyagi) 	AST	Break	ASW	T/C	Anuradha   (es.arawat) 	AST
4	2	01/04/2013	582	15	567	119	1	330	6	35		#¡VALOR!	01:00:43	00:29:13	00:11:57	33	#¡VALOR!
4	3	02/04/2013	605	17	588	96	0	349	5	29		#¡VALOR!	01:11:12	00:08:03	01:11:02	34	#¡VALOR!
4	4	03/04/2013	647	25	622	89	2	392	16			#¡VALOR!					#¡VALOR!

[/pre]
-----


Formula for M17 (AST) is:

=SUMAPRODUCTO((CD[Date]=$D17)*(CD[Session Time])*(CD[Agent]=$L$16))/$L17 -----> in english: =SUMPRODUCT((CD[Date]=$D17)*(CD[Session Time])*(CD[Agent]=$L$16))/$L17


Formula for R17 (2nd AST) is:

=SUMAPRODUCTO((CD[Date]=$D17)*(CD[Session Time])*(CD[Agent]=$Q$16))/$Q17 -----> in english: =SUMPRODUCT((CD[Date]=$D17)*(CD[Session Time])*(CD[Agent]=$Q$16))/$Q17


That's to say, they're equal.


Regards!

 

[shamitb]

Thanks SirJB7 for posting the table. I tried adding the table in the forum but wasn't able to.


Also, thanks Luke, I did almost the same in my formula, I have a certain date, A particular agent name (whose data I am looking for) and overall product of time spent by him on chats.


I am not sure why I am getting this error or is there a workaround?

 

[SirJB7]

Hi, shamitb!

No problem, man, I'm the personal, corporate, private, public & executive assistant of Mr. Luke M... M of Monk! I'm wondering if like Adrian (Tony Shalhoub) in the TV series he has an OCD about files... Then will I be his nurse, Sharona? Better I stop wondering.

Regards!

 

[shamitb]

hahahaha you guys rock. I have read few forums and you guys have done a great job


However, still I am not able to resolve the problem, any suggestion?

 

[SirJB7]

Hi, shamitb!


I gave a look, more than one in fact, and I didn't find out what's going on. Later or tomorrow I'll give another try.


In the meanwhile, you have 2 cells with elapsed time (I think it was that) with wrong format: 00:00:-4 & 00:00:0-2. In the same column there are a lot of blanks, perhaps you could edit the formula so as to have a zero value instead of an empty cell.


If you think that it'd help, check if any other cases with other data columns, as I haven't done it.


Regards!

 

[shamitb]

SirJB7 rocks.. I believe that was the issue.. this didn't click my mind.. I'll keep a note of this one.. I corrected the format and bingo.. it worked..


Thanks a ton !!!!

 

[SirJB7]

Hi, shamitb!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!