Sumifs for an array of date ranges?

kingkong1028

New Member
So alright, this is a little hard to explain, but I will try my best: I have 3 columns of dates: A, B and C. I have another column of the money in D. I would like to be able to do this: if a value in C2, says 6/16/2014, is in between the dates in A2 and B2, says 6/15/2014 and 6/18/2014, then D2, says $ 1 MM will be added to the final sum. Do this until we get through all the ranges of dates: say from A2-B2 to A100-B100. Is there an easy way (one formula) to do this. If you need more clarification, let me know. So in short, I need to do sumifs of the money in column D if a value of date in a cell in column C is within an array of a range of dates in column A and B.
 

Andrew Poulsom

MrExcel MVP
This seems to work:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Start</td><td style=";">End</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">10</td><td style="text-align: right;;">110</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">15/06/2014</td><td style="text-align: right;;">18/06/2014</td><td style="text-align: right;;">19/06/2014</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">15/07/2014</td><td style="text-align: right;;">16/07/2014</td><td style="text-align: right;;">13/07/2014</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">23/08/2014</td><td style="text-align: right;;">25/08/2014</td><td style="text-align: right;;">16/07/2014</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15/08/2014</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24/08/2014</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><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 width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIFS(<font color="Red">D1:D6,C1:C6,">="&A2:A4,C1:C6,"<="&B2:B4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

kingkong1028

New Member
Thank you for the quick response. However, what I meant was: if C1 is in between the range of a2-b2, a3-b3, a4-b4, then sum up all of those associated D cells for C1. Then repeat for C2, C3 to C6. So we are doing a sumifs where the criteria range is 1 value, while the criteria is a range of ranges of dates.
Start Date
End Date
Date
Money
Final
6/16/2014
7/1/2014
6/16/2014
10
150
6/16/2014
6/23/2014
6/17/2014
20
150
6/16/2014
6/30/2014
6/18/2014
30
150
6/16/2014
7/15/2014
7/2/2014
40
90
6/16/2014
7/16/2014
7/31/2014
50

<tbody>
</tbody>
So for instance, since for the date: 7/31/2014 is not in between the ranges of any of the dates, the final money is 0. The date 7/2/2014, is between the ranges of 6/16 to 7/15 and 6/16 to 7/16 so the final sum is 40+50 = 90.
 
Last edited:

Andrew Poulsom

MrExcel MVP
Like this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Start</td><td style=";">End</td><td style=";">Date</td><td style=";">Money</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">01/07/2014</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">10</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">23/06/2014</td><td style="text-align: right;;">17/06/2014</td><td style="text-align: right;;">20</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">30/06/2014</td><td style="text-align: right;;">18/06/2014</td><td style="text-align: right;;">30</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">15/07/2014</td><td style="text-align: right;;">02/07/2014</td><td style="text-align: right;;">40</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">16/06/2014</td><td style="text-align: right;;">16/07/2014</td><td style="text-align: right;;">31/07/2014</td><td style="text-align: right;;">50</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><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 width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=SUMIFS(<font color="Blue">D$2:D$6,A$2:A$6,"<="&C2,B$2:B$6,">="&C2</font>)</td></tr></tbody></table></td></tr></table><br />
 

kingkong1028

New Member
Like this?

Excel 2010
ABCDE
1StartEndDateMoney
216/06/201401/07/201416/06/201410150
316/06/201423/06/201417/06/201420150
416/06/201430/06/201418/06/201430150
516/06/201415/07/201402/07/20144090
616/06/201416/07/201431/07/2014500

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=SUMIFS(D$2:D$6,A$2:A$6,"<="&C2,B$2:B$6,">="&C2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
The formula didn't work for me :(
 

Some videos you may like

This Week's Hot Topics

Top