SUMIF with dates... help

J

Jazzer

Board Regular
Joined
Jun 14, 2011
Messages
71
Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
I have been using the following sumif<o:p></o:p>
<o:p></o:p>
=SUMIF('NOMAD DATA'!E11:E390,"<="&D10,'NOMAD DATA'!I11:I390)<o:p></o:p>
<o:p></o:p>
Cell D10 = the desired date on which we wish to know the result<o:p></o:p>
‘NOMAD DATA’ Column E = various dates<o:p></o:p>
‘NOMAD DATA’ Column I = the amounts we are looking to sum<o:p></o:p>
<o:p></o:p>
This has been working fine<o:p></o:p>
<o:p></o:p>
However I now have another variant to contend with ‘NOMAD DATA’ Column F contains dates in some of the rows<o:p></o:p>
I now need to SUM ‘NOMAD DATA’ Column I – excluding any amounts which have a date in column F before or equal to the date in cell D10 <o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Thanks<o:p></o:p>
James<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
 
Sort by date Sort by votes
Jazzer said:
thanks - that's done the trick on that front.

I'm not convinced that my sum is correct.
I think my explanation of what was actually needed was unclear

the sum should do the following

SUM NOMADDATA colomn I when NOMADDATA colomn E is "<=" to D10 but not if NOMADDATA colomn F is "<=" to D10 (only when the first past of the query is matched though)
Click to expand...
Let's assume D10 = 1/1/2011

Based on this data what result would you expect:

Book1
EFI
112/11/20041/5/200018
1212/8/20115/9/20137
139/4/20083/18/201162
144/10/20068/8/200691
158/7/20136/20/20123
165/19/200712/8/200944
176/10/20029/17/201129
1812/20/20033/10/201228
1911/19/20092/18/200897
2010/28/20143/15/201057
214/4/201312/11/200612
225/7/20076/29/200638
2311/24/20028/4/200299
241/5/20116/27/200250
252/23/20139/4/201096
Sheet1
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
T. Valko said:
Let's assume D10 = 1/1/2011

Based on this data what result would you expect:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 84px"><COL style="WIDTH: 84px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>E</TD><TD>F</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2/11/2004</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1/5/2000</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12/8/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5/9/2013</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/4/2008</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3/18/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">62</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4/10/2006</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/8/2006</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">91</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/7/2013</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">6/20/2012</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5/19/2007</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12/8/2009</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">44</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">6/10/2002</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/17/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">29</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12/20/2003</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3/10/2012</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">28</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">11/19/2009</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2/18/2008</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">97</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">10/28/2014</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3/15/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">57</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4/4/2013</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12/11/2006</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5/7/2007</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">6/29/2006</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">38</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">11/24/2002</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/4/2002</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">99</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1/5/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">6/27/2002</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">50</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2/23/2013</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/4/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">96</TD></TR></TBODY></TABLE>
Click to expand...

The date in colomn F will never be less than the date in colomn E on the same row.

Colomn E contains a start date, Colomn F contains the end date
There will also be instances where we have a start date (E) but no end date (F)

my sum will need to include I when E is less than or equal to D10 but not if F (on the same row) is also less than or equal to D10

so if D10 = 06-04-2011

<TABLE style="WIDTH: 262pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=349 border=0><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 90pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=120 height=16>E</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=119>F</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=110>I</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>08/03/2011 00:00</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">-4,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>11/03/2011 00:00</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-2,500,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>14/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">14/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">16,200,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>14/03/2011 00:00</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">14/04/2011 00:00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-16,200,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>28/03/2011 00:00</TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-2,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>28/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>28/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">14/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>30/03/2011 00:00</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">06/04/2011 00:00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">31,000,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>30/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">06/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">33,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>30/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">06/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">-33,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>31/03/2011 00:00</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-2,500,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>31/03/2011 00:00</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2,500,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">28,870,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-28,870,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">3,000,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">23,000,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">4,945,000.00</TD></TR></TBODY></TABLE>

I would expect +26,445,000.00

Thanks
 
Upvote 0
Jazzer said:
The date in colomn F will never be less than the date in colomn E on the same row.

Colomn E contains a start date, Colomn F contains the end date
There will also be instances where we have a start date (E) but no end date (F)

my sum will need to include I when E is less than or equal to D10 but not if F (on the same row) is also less than or equal to D10

so if D10 = 06-04-2011

<TABLE style="WIDTH: 262pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=349 border=0><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 90pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=120 height=16>E</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=119>F</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=110>I</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>08/03/2011 00:00</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">-4,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>11/03/2011 00:00</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-2,500,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>14/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">14/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">16,200,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>14/03/2011 00:00</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">14/04/2011 00:00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-16,200,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>28/03/2011 00:00</TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-2,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>28/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>28/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">14/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>30/03/2011 00:00</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">06/04/2011 00:00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">31,000,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>30/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">06/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">33,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>30/03/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">06/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">-33,900,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>31/03/2011 00:00</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-2,500,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>31/03/2011 00:00</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2,500,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">28,870,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-28,870,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">3,000,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">07/04/2011 00:00</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">23,000,000.00</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" height=16>06/04/2011 00:00</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">4,945,000.00</TD></TR></TBODY></TABLE>

I would expect +26,445,000.00

Thanks
Click to expand...
OK, try this...

=SUMIF('NOMAD DATA'!E11:E500,"<="&D10,'NOMAD DATA'!I11:I500)-SUMIF('NOMAD DATA'!F11:F500,"<="&D10,'NOMAD DATA'!I11:I500)

Format as General or Number.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
70
Cubist
Cubist
S
  • Solved
SumIf problem
Replies
2
Views
363
sharshra
S
J
  • Solved
Using Month query inside sumif formula
Replies
6
Views
732
Jpull
J
T
  • Solved
VBA Sumif Function
Replies
4
Views
664
TaskMaster
T
8
  • Solved
How to avoid spill error on Sumif
Replies
4
Views
466
Eric W
Eric W

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

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