Array formula help

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84
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>
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84
I changed it from >= to <= and it the value in AB5 went from 92 to -1952
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Jun 24, 2010
Messages
84

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Jun 24, 2010
Messages
84
That appears to have corrected the errors. Thanks once again Barry
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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
Top