Sumifs for an array of date ranges?

[kingkong1028]

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]

This seems to work:

Excel 2010
	A	B	C	D	E
1	Start	End	16/06/2014	10	110
2	15/06/2014	18/06/2014	19/06/2014	20
3	15/07/2014	16/07/2014	13/07/2014	30
4	23/08/2014	25/08/2014	16/07/2014	40
5			15/08/2014	50
6			24/08/2014	60
Sheet1

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(SUMIFS(D1:D6,C1:C6,">="&A2:A4,C1:C6,"<="&B2:B4))

 

[kingkong1028]

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.[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]Date
[/TD]
[TD]Money
[/TD]
[TD]Final
[/TD]
[/TR]
[TR]
[TD]6/16/2014
[/TD]
[TD]7/1/2014
[/TD]
[TD]6/16/2014
[/TD]
[TD]10
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]6/16/2014
[/TD]
[TD]6/23/2014
[/TD]
[TD]6/17/2014
[/TD]
[TD]20
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]6/16/2014
[/TD]
[TD]6/30/2014
[/TD]
[TD]6/18/2014
[/TD]
[TD]30
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]6/16/2014
[/TD]
[TD]7/15/2014
[/TD]
[TD]7/2/2014
[/TD]
[TD]40
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]6/16/2014
[/TD]
[TD]7/16/2014
[/TD]
[TD]7/31/2014
[/TD]
[TD]50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[Andrew Poulsom]

Like this?

Excel 2010
	A	B	C	D	E
1	Start	End	Date	Money
2	16/06/2014	01/07/2014	16/06/2014	10	150
3	16/06/2014	23/06/2014	17/06/2014	20	150
4	16/06/2014	30/06/2014	18/06/2014	30	150
5	16/06/2014	15/07/2014	02/07/2014	40	90
6	16/06/2014	16/07/2014	31/07/2014	50	0
Sheet1

Cell Formulas
Range	Formula
E2	=SUMIFS(D$2:D$6,A$2:A$6,"<="&C2,B$2:B$6,">="&C2)

 

[kingkong1028]

Like this?

Excel 2010

	A	B	C	D	E
Start	End	Date	Money

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]16/06/2014[/TD]
[TD="align: right"]01/07/2014[/TD]
[TD="align: right"]16/06/2014[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]16/06/2014[/TD]
[TD="align: right"]23/06/2014[/TD]
[TD="align: right"]17/06/2014[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]16/06/2014[/TD]
[TD="align: right"]30/06/2014[/TD]
[TD="align: right"]18/06/2014[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]16/06/2014[/TD]
[TD="align: right"]15/07/2014[/TD]
[TD="align: right"]02/07/2014[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]90[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]16/06/2014[/TD]
[TD="align: right"]16/07/2014[/TD]
[TD="align: right"]31/07/2014[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=SUMIFS(D$2:D$6,A$2:A$6,"<="&C2,B$2:B$6,">="&C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The formula didn't work for me

 

[Andrew Poulsom]

As you can see from my example it works for me. In what way doesn't it work for you?

 

[kingkong1028]

For the first row I got 100 instead of 150 and I copied and pasted your formula in.

 

[Andrew Poulsom]

With the same data as I posted? Note that I am using UK style dates and the data is in A1:D6 with headings in row 1.