sumproduct and networkdays

[Kris75]

Hi guys
I am using the following formula to count events when less than 24 hours has passed from first attempt (col b) and Date opened (Col A) occured.


How can i use the same formula but to calculation working days only?

=SUMPRODUCT(--(A11:A1000<>0),--($B$11:$B$1000-$A$11:$A$1000<1))

<TABLE style="WIDTH: 202pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=270 x:str><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" span=2 width=135><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 101pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl25 height=20 width=135>Date Opened</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 101pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl25 width=135>First Attempt</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl26 height=20 width=135 x:num="40704.752569444441">10/06/2011 18:03</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND: red; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid; mso-ignore: style; mso-pattern: black none" class=xl26 width=135 x:num="40707.411539351851">13/06/2011 09:52</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl26 height=20 width=135 x:num="40707.344930555555">13/06/2011 08:16</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND: #ccffcc; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid; mso-ignore: style; mso-pattern: black none" class=xl26 width=135 x:num="40707.412280092591">13/06/2011 09:53</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl26 height=20 width=135 x:num="40707.362800925926">13/06/2011 08:42</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND: #ccffcc; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid; mso-ignore: style; mso-pattern: black none" class=xl26 width=135 x:num="40707.413865740738">13/06/2011 09:55</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl26 height=20 width=135 x:num="40707.368078703701">13/06/2011 08:50</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND: #ccffcc; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid; mso-ignore: style; mso-pattern: black none" class=xl26 width=135 x:num="40707.421493055554">13/06/2011 10:06</TD></TR></TBODY></TABLE>

 

[barry houdini]

Which version of Excel? In Excel 2007 and later this should work

=SUMPRODUCT(--(A11:A1000<>0),--(NETWORKDAYS($A$11:$A$1000+0,$B$11:$B$1000+0)<2))

or in Excel 2003 try

=SUMPRODUCT(($A$11:$A$1000>0)*(MMULT(INT((WEEKDAY($A$11:$A$1000+7-{2,3,4,5,6})+$B$11:$B$1000-$A$11:$A$1000)/7),{1;1;1;1;1})<2))

That's almost at the limit for the number of rows that can be accommodated by MMULT in 2003......