Sumproduct including text

T

tajumaru

New Member
Joined
Jun 1, 2011
Messages
11
Hi,

I am new to this forum and would to post a question regarding sumproduct. I was able to do this Open Office Calc but unable to replicate the results in Excel 2007.

This is the formula: =SUMPRODUCT(1+($B$13:$B$39=$J$12),1*($C$13:$C$39=$I14),1*($G$13:$G$39)) wherein B13:B39 are dates, C13:C39 are text (categories) and G13:G39 are numbers and J12 and I14 are the criteria . Using this formula in Open Office Calc returns the result as numbers while in Excel 2007, this same formula always returns a result of zero "0". I read somewhere that Excel treats text as zero and therefore this formula will result in zero. I am not sure about that as I am not an expert in Excel.

Is there a way for me to circumvent Excel in the way text are treated and make it return the correct results like in Open Office Calc. Please help as I really need this resolved. Any help or suggestions will be greatly appreciated. Thanks in advance and more power to your site.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Welcome to MrExcel board...

I'm not familiar with the other program you talked about but I think your formula will work like this
Code: 
=SUMPRODUCT(--($B$13:$B$39=$J$12),--($C$13:$C$39=$I14),($G$13:$G$39))
 
Last edited:
Upvote 0
Would SUMIFS work?

=SUMIFS($G$13:$G$39,$B$13:$B$39,$J$12,$C$13:$C$39,$I14)
 
Upvote 0
Thank you texasalynn and anglais428 for your quick response. I tried both of your suggestions but still the results are returned as zero. This is really frustrating as I am really out of ideas.

Texasalynn, Open Office Calc (.ods) is a freeware that is an alternative to Office suites.

Hopefully, you have more ideas to help out a newbie like me. Again, thank you and have a good day!
 
Upvote 0
tajumaru said:
Thank you texasalynn and anglais428 for your quick response. I tried both of your suggestions but still the results are returned as zero. This is really frustrating as I am really out of ideas.

Texasalynn, Open Office Calc (.ods) is a freeware that is an alternative to Office suites.

Hopefully, you have more ideas to help out a newbie like me. Again, thank you and have a good day!
Click to expand...

Care to post the formula exactly as you have it in OpenOffice.org Calc?
 
Upvote 0
Also, some sample data and expected result. I get a strange result when I try your formula from Post #1 in an .odf file ! You may in fact want to also say what version of OOorg you are using.
 
Upvote 0
Hi,

This is how it appears in OOorg (ver 3.1): =SUMPRODUCT((J$15=$B$16:$B$2572)*($I16=$C$16:$C$2572)*($G$16:$G$2572)) and here is the sample data.

<TABLE style="WIDTH: 576pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=766 border=0><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: #0f253f" width=84 height=22>DATE</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 139pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0f253f" width=185>CATEGORY</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 161pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0f253f" width=215>COMMENTS</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0f253f" width=94>START</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0f253f" width=94>END</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #0f253f" width=94>DURATION</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">TRAINING</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">8:05:34 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11:05:50 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">180.28</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2ND BREAK</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">6:42:24 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">8:05:34 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">83.17</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2ND BREAK</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">6:41:30 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">6:42:24 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.89</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LUNCH</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">6:33:05 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">6:41:30 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">8.43</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">IDLE TIME</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:32:06 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">6:33:05 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">60.98</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">TRAINING</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:29:06 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:32:06 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">3.00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SYSTEM DOWNTIME</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:17:39 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:29:06 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">11.46</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LUNCH</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:05:10 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:17:39 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">12.48</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">1ST BREAK</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:02:45 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:05:10 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">2.42</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">MANAGEMENT</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:02:27 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:02:45 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.30</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">IDLE TIME</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:01:46 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:02:27 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.67</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">WORK -QC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:00:55 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:01:46 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.85</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">WORK -QC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5:00:55 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">3.63</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">WORK – PROCESS</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">START SHIFT</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.00</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>2-Jun</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">START SHIFT</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">4:57:17 PM</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.00</TD></TR></TBODY></TABLE>

and this is the table for the result summary:
<TABLE style="WIDTH: 253pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=337 border=0><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" span=4 width=40><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #0f253f" width=131 height=22> </TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #0f253f" width=46>1-Jun</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #0f253f" width=40>2-Jun</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #0f253f" width=40>3-Jun</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #0f253f" width=40>4-Jun</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #0f253f" width=40>5-Jun</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>WORK – PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>WORK -QC</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>QUALITY CHECKING</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>IDLE TIME</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>COACHING</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>LOCKOUT</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>MANAGEMENT</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>MEETING</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>SYSTEM DOWNTIME</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>TRAINING</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>LUNCH</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>1ST BREAK</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>2ND BREAK</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR></TBODY></TABLE>

I want to run this is Excel 2007 but I get a zero using the formula above. Thank you very much.
 
Upvote 0
this works for me
Excel Workbook
IJKLMN
151-Jun2-Jun3-Jun4-Jun5-Jun
16WORK PROCESS00000
17WORK -QC04.48000
18QUALITY CHECKING00000
19IDLE TIME061.65000
20COACHING00000
21LOCKOUT00000
22MANAGEMENT00.3000
23MEETING00000
24SYSTEM DOWNTIME011.46000
25TRAINING0183.28000
26LUNCH020.91000
271ST BREAK02.42000
282ND BREAK084.06000
Sheet1
Excel 2003
Cell Formulas
RangeFormula
J16=SUMPRODUCT(--($B$16:$B$2572=J$15),--($C$16:$C$2572=$I16),($G$16:$G$2572))
K16=SUMPRODUCT(--($B$16:$B$2572=K$15),--($C$16:$C$2572=$I16),($G$16:$G$2572))
L16=SUMPRODUCT(--($B$16:$B$2572=L$15),--($C$16:$C$2572=$I16),($G$16:$G$2572))
M16=SUMPRODUCT(--($B$16:$B$2572=M$15),--($C$16:$C$2572=$I16),($G$16:$G$2572))
N16=SUMPRODUCT(--($B$16:$B$2572=N$15),--($C$16:$C$2572=$I16),($G$16:$G$2572))
 
Upvote 0
Hi texasalynn,

Sorry for the late reply. Weekend came up and never had a chance to look at this.

I tried your formula in both 2003 and 2007 Excel but got the same result as before, zero. I copied the formula to my spreadsheet but I have different results than yours. Arrrghh! This is confusing and frustrating. I do not know what is wrong especially knowing that yours is working.

Would you be able to send me a copy of your file? I do not know if that is too much to ask or a violation of the rules of this forum. If it is, please kindly disregard.

I will send you my email via pm.

Thank you.
 
Upvote 0
Your formula works for me in both OOorg 3.2 and in Excel 2010 (with an .xls file format). I didn't even need to change it for excel - just open the .ods or the .xls (both were created in OOorg).

Sample of both (slightly different placement of formulas with less data):
Sample Files

md5sum (zip file): 3a77ad522d17f540b52d911caa127799
sha256sum (zip file): 9ab5e2fcbbf6bb5c0fe7820f1cb3f590a0b4182d96857b682f36fecd2c76bc52

My Sample Data (happens to be exactly the same each day as I only copied/pasted the first day for the other days):
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">CATEGORY</td><td style=";">COMMENTS</td><td style=";">START</td><td style=";">END</td><td style=";">DURATION</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">TRAINING</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">08:05:34 PM</td><td style="font-weight: bold;text-align: right;;">11:05:50 PM</td><td style="font-weight: bold;text-align: right;;">180.27</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">2ND BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:42:24 PM</td><td style="font-weight: bold;text-align: right;;">08:05:34 PM</td><td style="font-weight: bold;text-align: right;;">83.17</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">2ND BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:41:30 PM</td><td style="font-weight: bold;text-align: right;;">06:42:24 PM</td><td style="font-weight: bold;text-align: right;;">0.90</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">LUNCH</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:33:05 PM</td><td style="font-weight: bold;text-align: right;;">06:41:30 PM</td><td style="font-weight: bold;text-align: right;;">8.42</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">IDLE TIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:32:06 PM</td><td style="font-weight: bold;text-align: right;;">06:33:05 PM</td><td style="font-weight: bold;text-align: right;;">60.98</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">TRAINING</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:29:06 PM</td><td style="font-weight: bold;text-align: right;;">05:32:06 PM</td><td style="font-weight: bold;text-align: right;;">3.00</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">SYSTEM DOWNTIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:17:39 PM</td><td style="font-weight: bold;text-align: right;;">05:29:06 PM</td><td style="font-weight: bold;text-align: right;;">11.45</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">LUNCH</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:05:10 PM</td><td style="font-weight: bold;text-align: right;;">05:17:39 PM</td><td style="font-weight: bold;text-align: right;;">12.48</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">1ST BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:02:45 PM</td><td style="font-weight: bold;text-align: right;;">05:05:10 PM</td><td style="font-weight: bold;text-align: right;;">2.42</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">MANAGEMENT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:02:27 PM</td><td style="font-weight: bold;text-align: right;;">05:02:45 PM</td><td style="font-weight: bold;text-align: right;;">0.30</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">IDLE TIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:01:46 PM</td><td style="font-weight: bold;text-align: right;;">05:02:27 PM</td><td style="font-weight: bold;text-align: right;;">0.68</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">WORK -QC</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:00:55 PM</td><td style="font-weight: bold;text-align: right;;">05:01:46 PM</td><td style="font-weight: bold;text-align: right;;">0.85</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">WORK -QC</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">05:00:55 PM</td><td style="font-weight: bold;text-align: right;;">3.63</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">WORK – PROCESS</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">START SHIFT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="font-weight: bold;text-align: right;;">06/02/11</td><td style="font-weight: bold;;">START SHIFT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">TRAINING</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">08:05:34 PM</td><td style="font-weight: bold;text-align: right;;">11:05:50 PM</td><td style="font-weight: bold;text-align: right;;">180.27</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">2ND BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:42:24 PM</td><td style="font-weight: bold;text-align: right;;">08:05:34 PM</td><td style="font-weight: bold;text-align: right;;">83.17</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">2ND BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:41:30 PM</td><td style="font-weight: bold;text-align: right;;">06:42:24 PM</td><td style="font-weight: bold;text-align: right;;">0.90</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">LUNCH</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:33:05 PM</td><td style="font-weight: bold;text-align: right;;">06:41:30 PM</td><td style="font-weight: bold;text-align: right;;">8.42</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">IDLE TIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:32:06 PM</td><td style="font-weight: bold;text-align: right;;">06:33:05 PM</td><td style="font-weight: bold;text-align: right;;">60.98</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">TRAINING</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:29:06 PM</td><td style="font-weight: bold;text-align: right;;">05:32:06 PM</td><td style="font-weight: bold;text-align: right;;">3.00</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">SYSTEM DOWNTIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:17:39 PM</td><td style="font-weight: bold;text-align: right;;">05:29:06 PM</td><td style="font-weight: bold;text-align: right;;">11.45</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">LUNCH</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:05:10 PM</td><td style="font-weight: bold;text-align: right;;">05:17:39 PM</td><td style="font-weight: bold;text-align: right;;">12.48</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">1ST BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:02:45 PM</td><td style="font-weight: bold;text-align: right;;">05:05:10 PM</td><td style="font-weight: bold;text-align: right;;">2.42</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">MANAGEMENT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:02:27 PM</td><td style="font-weight: bold;text-align: right;;">05:02:45 PM</td><td style="font-weight: bold;text-align: right;;">0.30</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">IDLE TIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:01:46 PM</td><td style="font-weight: bold;text-align: right;;">05:02:27 PM</td><td style="font-weight: bold;text-align: right;;">0.68</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">WORK -QC</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:00:55 PM</td><td style="font-weight: bold;text-align: right;;">05:01:46 PM</td><td style="font-weight: bold;text-align: right;;">0.85</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">WORK -QC</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">05:00:55 PM</td><td style="font-weight: bold;text-align: right;;">3.63</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">WORK – PROCESS</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">START SHIFT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style="font-weight: bold;text-align: right;;">06/03/11</td><td style="font-weight: bold;;">START SHIFT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">TRAINING</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">08:05:34 PM</td><td style="font-weight: bold;text-align: right;;">11:05:50 PM</td><td style="font-weight: bold;text-align: right;;">180.27</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">2ND BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:42:24 PM</td><td style="font-weight: bold;text-align: right;;">08:05:34 PM</td><td style="font-weight: bold;text-align: right;;">83.17</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">2ND BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:41:30 PM</td><td style="font-weight: bold;text-align: right;;">06:42:24 PM</td><td style="font-weight: bold;text-align: right;;">0.90</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">LUNCH</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">06:33:05 PM</td><td style="font-weight: bold;text-align: right;;">06:41:30 PM</td><td style="font-weight: bold;text-align: right;;">8.42</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">IDLE TIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:32:06 PM</td><td style="font-weight: bold;text-align: right;;">06:33:05 PM</td><td style="font-weight: bold;text-align: right;;">60.98</td></tr><tr><td style="color: #161120;text-align: center;">39</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">TRAINING</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:29:06 PM</td><td style="font-weight: bold;text-align: right;;">05:32:06 PM</td><td style="font-weight: bold;text-align: right;;">3.00</td></tr><tr><td style="color: #161120;text-align: center;">40</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">SYSTEM DOWNTIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:17:39 PM</td><td style="font-weight: bold;text-align: right;;">05:29:06 PM</td><td style="font-weight: bold;text-align: right;;">11.45</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">LUNCH</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:05:10 PM</td><td style="font-weight: bold;text-align: right;;">05:17:39 PM</td><td style="font-weight: bold;text-align: right;;">12.48</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">1ST BREAK</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:02:45 PM</td><td style="font-weight: bold;text-align: right;;">05:05:10 PM</td><td style="font-weight: bold;text-align: right;;">2.42</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">MANAGEMENT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:02:27 PM</td><td style="font-weight: bold;text-align: right;;">05:02:45 PM</td><td style="font-weight: bold;text-align: right;;">0.30</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">IDLE TIME</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:01:46 PM</td><td style="font-weight: bold;text-align: right;;">05:02:27 PM</td><td style="font-weight: bold;text-align: right;;">0.68</td></tr><tr><td style="color: #161120;text-align: center;">45</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">WORK -QC</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">05:00:55 PM</td><td style="font-weight: bold;text-align: right;;">05:01:46 PM</td><td style="font-weight: bold;text-align: right;;">0.85</td></tr><tr><td style="color: #161120;text-align: center;">46</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">WORK -QC</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">05:00:55 PM</td><td style="font-weight: bold;text-align: right;;">3.63</td></tr><tr><td style="color: #161120;text-align: center;">47</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">WORK – PROCESS</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">48</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">START SHIFT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">49</td><td style="font-weight: bold;text-align: right;;">06/04/11</td><td style="font-weight: bold;;">START SHIFT</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">04:57:17 PM</td><td style="font-weight: bold;text-align: right;;">0.00</td></tr></tbody></table>
1​




My sample results:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">
</td><td style="text-align: right;;">06/02/11</td><td style="text-align: right;;">06/03/11</td><td style="text-align: right;;">06/04/11</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">WORK – PROCESS</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">WORK -QC</td><td style="text-align: right;;">4.48</td><td style="text-align: right;;">4.48</td><td style="text-align: right;;">4.48</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">QUALITY CHECKING</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">IDLE TIME</td><td style="text-align: right;;">61.67</td><td style="text-align: right;;">61.67</td><td style="text-align: right;;">61.67</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">COACHING</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">LOCKOUT</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">MANAGEMENT</td><td style="text-align: right;;">0.30</td><td style="text-align: right;;">0.30</td><td style="text-align: right;;">0.30</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">MEETING</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0.00</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">SYSTEM DOWNTIME</td><td style="text-align: right;;">11.45</td><td style="text-align: right;;">11.45</td><td style="text-align: right;;">11.45</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">TRAINING</td><td style="text-align: right;;">183.27</td><td style="text-align: right;;">183.27</td><td style="text-align: right;;">183.27</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">LUNCH</td><td style="text-align: right;;">20.90</td><td style="text-align: right;;">20.90</td><td style="text-align: right;;">20.90</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">1ST BREAK</td><td style="text-align: right;;">2.42</td><td style="text-align: right;;">2.42</td><td style="text-align: right;;">2.42</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">2ND BREAK</td><td style="text-align: right;;">84.07</td><td style="text-align: right;;">84.07</td><td style="text-align: right;;">84.07</td></tr></tbody></table>
1​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">J2</th><td style="text-align:left">=SUMPRODUCT(($I2=$B$2:$B$49)*(J$1=$A$2:$A$49)*($F$2:$F$49))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">K2</th><td style="text-align:left">=SUMPRODUCT(($I2=$B$2:$B$49)*(K$1=$A$2:$A$49)*($F$2:$F$49))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">L2</th><td style="text-align:left">=SUMPRODUCT(($I2=$B$2:$B$49)*(L$1=$A$2:$A$49)*($F$2:$F$49))</td></tr></tbody></table></td></tr></tbody></table>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
SUMPRODUCT CONTINUED
Replies
6
Views
384
Alex Blakenburg
A
B
  • Solved
SUMPRODUCT with FILTER Criteria
Replies
3
Views
363
Fluff
Fluff
A
  • Solved
Excel formula does not return expected result when encountering a cell that includes text in a formula reference
2
Replies
11
Views
1K
Joe4
Joe4
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
285
Fluff
Fluff
S
  • Question Question
Pro Rata a Sumproduct Calculation
Replies
1
Views
323
myall_blues
myall_blues

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top