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.

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.

 

[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
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

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

Sheet1​

Worksheet Formulas Cell Formula 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

 

[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.