Array formula help

[NadoDude]

I am using an array formula to calculate the number of days used between 2 dates which the user defines. The formula works great except it is adding days which occur after the end date (cell AG2) that the user inputs. I need to add something to the array formula which does not calculate events which started after the user defined end date (cell AG2).

<font size="2">Worksheet: Alpha Roster UsedRange: $A$1:$AH$2083 Range: $AB$1:$AH$6</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AB</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AC</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AD</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AE</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AF</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AG</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">AH</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAB1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAC1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAD1_705"></a><font size="2">User Defined Periods</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAE1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAF1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAG1_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAH1_705"></a><font size="2"># Days</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAB2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAC2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAD2_705"></a><font size="2">From:</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAE2_705"></a><font size="2">4/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAF2_705"></a><font size="2">To:</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAG2_705"></a><font size="2">6/30/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAH2_705"></a><font size="2"><a title="=(AG2-AE2)+1" href="#fAH2_705">91</a></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAB3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAC3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAD3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAE3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAF3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAG3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAH3_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">4</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAB4_705"></a><font size="2">User Defined</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAC4_705"></a><font size="2">Notes</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAD4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAE4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAF4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAG4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAH4_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAB5_705"></a><font size="2"><a title="=SUM(IF(('ITEMPO Events'!$I$2:$I$7987=$A5)*(('ITEMPO Events'!$O$2:$O$7987>=($AG$2-$AH$2)+1)+('ITEMPO Events'!$O$2:$O$7987="")>0),IF('ITEMPO Events'!$O$2:$O$7987="",($AG$2)+1,'ITEMPO Events'!$O$2:$O$7987)-IF('ITEMPO Events'!$M$2:$M$7987>=($AG$2-$AH$2)+1,'ITEMPO Events'!$M$2:$M$7987,($AG$2-$AH$2)+1),0))" href="#fAB5_705">92</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAC5_705"></a><font size="2">Has event that ended 7/2/10</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAD5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAE5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAF5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAG5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAH5_705"></a><font size="2"></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><a name="cAB6_705"></a><font size="2"><a title="=SUM(IF(('ITEMPO Events'!$I$2:$I$7987=$A6)*(('ITEMPO Events'!$O$2:$O$7987>=($AG$2-$AH$2)+1)+('ITEMPO Events'!$O$2:$O$7987="")>0),IF('ITEMPO Events'!$O$2:$O$7987="",($AG$2)+1,'ITEMPO Events'!$O$2:$O$7987)-IF('ITEMPO Events'!$M$2:$M$7987>=($AG$2-$AH$2)+1,'ITEMPO Events'!$M$2:$M$7987,($AG$2-$AH$2)+1),0))" href="#fAB6_705">91</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAC6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAD6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAE6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAF6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAG6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cAH6_705"></a><font size="2"></font></td></table></div><p /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Address</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Value</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Formula</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fAH2_705"></a><font size="2"><a href="#cAH2_705">AH2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">91</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=(AG2-AE2)+1</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fAB5_705"></a><font size="2"><a href="#cAB5_705">AB5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">92</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=SUM(IF(('ITEMPO Events'!$I$2:$I$7987=$A5)*(('ITEMPO Events'!$O$2:$O$7987>=($AG$2-$AH$2)+1)+('ITEMPO Events'!$O$2:$O$7987="")>0),IF('ITEMPO Events'!$O$2:$O$7987="",($AG$2)+1,'ITEMPO Events'!$O$2:$O$7987)-IF('ITEMPO Events'!$M$2:$M$7987>=($AG$2-$AH$2)+1,'ITEMPO Events'!$M$2:$M$7987,($AG$2-$AH$2)+1),0))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fAB6_705"></a><font size="2"><a href="#cAB6_705">AB6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">91</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=SUM(IF(('ITEMPO Events'!$I$2:$I$7987=$A6)*(('ITEMPO Events'!$O$2:$O$7987>=($AG$2-$AH$2)+1)+('ITEMPO Events'!$O$2:$O$7987="")>0),IF('ITEMPO Events'!$O$2:$O$7987="",($AG$2)+1,'ITEMPO Events'!$O$2:$O$7987)-IF('ITEMPO Events'!$M$2:$M$7987>=($AG$2-$AH$2)+1,'ITEMPO Events'!$M$2:$M$7987,($AG$2-$AH$2)+1),0))</font></td></tr></table></div>

 

[Excelestial]

$O$2:$O$7987>= ...change to $O$2:$O$7987<=

 

[NadoDude]

I changed it from >= to <= and it the value in AB5 went from 92 to -1952

 

[barry houdini]

Try this version

=SUM(IF(('ITEMPO Events'!$I$2:$I$7987=$A5)*(('ITEMPO Events'!$O$2:$O$7987>=$AE$2)+('ITEMPO Events'!$O$2:$O$7987="")>0),IF('ITEMPO Events'!$O$2:$O$7987="",$AG$2+1,IF('ITEMPO Events'!$O$2:$O$7987>$AG$2,$AG$2,'ITEMPO Events'!$O$2:$O$7987))-IF('ITEMPO Events'!$M$2:$M$7987>=$AE$2,'ITEMPO Events'!$M$2:$M$7987,$AE$2),0))

 

[NadoDude]

your version changed the value in AB5 from 92 to 90. I should total 91. Also if the is no end date in column O, I am getting a negative number

 

[barry houdini]

OK, then try like this

=SUM(IF(('ITEMPO Events'!$I$2:$I$7987=$A5)*(('ITEMPO Events'!$O$2:$O$7987>=$AE$2)+('ITEMPO Events'!$O$2:$O$7987="")>0)*('ITEMPO Events'!$M$2:$M$7987<=$AG$2),IF('ITEMPO Events'!$O$2:$O$7987="",$AG$2+1,IF('ITEMPO Events'!$O$2:$O$7987>$AG$2,$AG$2+1,'ITEMPO Events'!$O$2:$O$7987))-IF('ITEMPO Events'!$M$2:$M$7987>=$AE$2,'ITEMPO Events'!$M$2:$M$7987,$AE$2),0))

 

[NadoDude]

That appears to have corrected the errors. Thanks once again Barry