SUMIFS with array that counts number of valid days within a date range

[PuGZoR]

Hi there,

I've tried to recreate two tables which I'm working with below:

[TABLE="width: 1000"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Finish Date[/TD]
[TD]Effective Days[/TD]
[TD]Total Amount[/TD]
[TD]Daily Amount[/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Start of Month[/TD]
[TD]End of Month[/TD]
[TD]Monthly Amount[/TD]
[/TR]
[TR]
[TD]04/09/2019[/TD]
[TD]22/10/2019[/TD]
[TD]49[/TD]
[TD]1000[/TD]
[TD]20.41[/TD]
[TD][/TD]
[TD]Sep-19[/TD]
[TD]01/09/2019[/TD]
[TD]30/09/2019[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]15/09/2019[/TD]
[TD]15/10/2019[/TD]
[TD]31[/TD]
[TD]1500[/TD]
[TD]48.39[/TD]
[TD][/TD]
[TD]Oct-19[/TD]
[TD]01/10/2019[/TD]
[TD]31/10/2019[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]01/10/2019[/TD]
[TD]24/12/2019[/TD]
[TD]85[/TD]
[TD]2000[/TD]
[TD]23.53[/TD]
[TD][/TD]
[TD]Nov-19[/TD]
[TD]01/11/2019[/TD]
[TD]30/11/2019[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dec-10[/TD]
[TD]01/12/2019[/TD]
[TD]31/12/2019[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

For the sake of this exercise, the first table would be A1:E4 (including headers) and the second table would be G1:J5.

I would like to have a formula which fills in the J column. This would have to go row-by-row in the first table, calculating how many days fall between the Start of Month and End of Month in the second table, multiply that by the amount in the Daily Amount column, then add that to the loop down the rest of the rows.

This is just a sample; unfortunately my real data is much larger!

I've tried to name the topic because I'm pretty confident it'll need an array formula, but I'm quite happy to accept a non-array formula if one exists. It might not be a SUMIFS either which works; that's fine too.

Please help!

 

[jimrward]

For completeness and understanding can you supply for your sample the values you would expect in j2:j5

I am guessing
j2 (30 * 20.41) = 612.30
J3 (31 * 48.39) + j2 = 2112.39
J4 (30 * 23.53) + j3 = 2818.29

 

[PuGZoR]

For completeness and understanding can you supply for your sample the values you would expect in j2:j5

I am guessing
j2 (30 * 20.41) = 612.30
J3 (31 * 48.39) + j2 = 2112.39
J4 (30 * 23.53) + j3 = 2818.29

Thanks for the reply Jim! Not quite - here's a breakdown below.

J2 I'd expect 1,325.21
551.02 from the A2:E2 row (27 days @ 20.41), plus,
774.19 from the A3:E3 row (16 days @ 48.39), plus,
0 from the A4:E4 row

J3 I'd expect 1,904.20
448.98 from the A2:E2 row (22 days @ 20.41), plus,
725.81 from the A3:E3 row (15 days @ 48.39), plus,
729.41 from the A4:E4 row (31 days @ 23.54)

J4 I'd expect 704.88
0 from the A2:E2 row, plus,
0 from the A3:E3 row, plus,
705.88 from the A4:E4 row (30 days @ 23.54)

J5 I'd expect 564.71
0 from the A2:E2 row, plus,
0 from the A3:E3 row, plus,
564.71 from the A4:E4 row (24 days @ 23.54)

That would equal to 4,500 in total (1,325.21 + 1,904.20 + 705.88 + 564.71).

 

[PuGZoR]

I've not got a solution, but I've created a formula which would solve just for J2 as above.

<h$2,b2<=i$2),true,false)=true,b2-h$2+1,if(if(and(a2><h$2,b2><h$2,a2><h$2,b2><h$2,b3<=i$2),true,false)=true,b3-h$2+1,if(if(and(a3><h$2,b3><h$2,a3><h$2,b3><h$2,b4<=i$2),true,false)=true,b4-h$2+1,if(if(and(a4><h$2,b4><h$2,a4><h$2,b4>=(IF(IF(AND(A2>=H$2,B2<=I$2),TRUE,FALSE)=TRUE,B2-A2+1,IF(IF(AND(A2<h$2,b2<=i$2),true,false)=true,b2-h$2+1,if(if(and(a2>=H$2,A2<=I$2,B2>I$2),TRUE,FALSE)=TRUE,I$2-A2+1,IF(IF(AND(A2<h$2,b2>I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E2)+(IF(IF(AND(A3>=H$2,B3<=I$2),TRUE,FALSE)=TRUE,B3-A3+1,IF(IF(AND(A3<h$2,b3<=i$2),true,false)=true,b3-h$2+1,if(if(and(a3>=H$2,A3<=I$2,B3>I$2),TRUE,FALSE)=TRUE,I$2-A3+1,IF(IF(AND(A3<h$2,b3>I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E3)+(IF(IF(AND(A4>=H$2,B4<=I$2),TRUE,FALSE)=TRUE,B4-A4+1,IF(IF(AND(A4<h$2,b4<=i$2),true,false)=true,b4-h$2+1,if(if(and(a4>=H$2,A4<=I$2,B4>I$2),TRUE,FALSE)=TRUE,I$2-A4+1,IF(IF(AND(A4<h$2,b4>I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E4)

It looks complex but really all it does is run through this logic for a single month (Sept 2019 in this example):
If A2 and B2 are within the month, just use the difference between A2 and B2 to calculate the number of days needed to multiply by E2
> If A2 is smaller than H2, and B2 is smaller than I2, use the difference between B2 and H2 as the number of days needed to multiple by E2
>> If A2 is larger than H2 and smaller than I2, but B2 is larger than I2, use the difference between A2 and I2 as the number of days needed to multiply by E2
>>> If A2 is smaller than H2 and B2 is larger than I2, use the difference between H2 and I2 as the number of days needed to multiple by E2
>>>> If none of the above apply then there are 0 days in the month that match

I just don't really know how to turn this into an array to go through columns A and B, one at a time and multiply the results by column E, relative to the dates which are in columns H and I.</h$2,b4></h$2,b4<=i$2),true,false)=true,b4-h$2+1,if(if(and(a4></h$2,b3></h$2,b3<=i$2),true,false)=true,b3-h$2+1,if(if(and(a3></h$2,b2></h$2,b2<=i$2),true,false)=true,b2-h$2+1,if(if(and(a2></h$2,b4></h$2,a4></h$2,b4></h$2,b4<=i$2),true,false)=true,b4-h$2+1,if(if(and(a4></h$2,b3></h$2,a3></h$2,b3></h$2,b3<=i$2),true,false)=true,b3-h$2+1,if(if(and(a3></h$2,b2></h$2,a2></h$2,b2></h$2,b2<=i$2),true,false)=true,b2-h$2+1,if(if(and(a2>

 

[PuGZoR]

=(IF(IF(AND(A2>=H$2,B2<=I$2),TRUE,FALSE)=TRUE,B2-A2+1,IF(IF(AND(A2<h$2,b2<=i$2),true,false)=true,b2-h$2+1,if(if(and(a2>=H$2,A2<=I$2,B2>I$2),TRUE,FALSE)=TRUE,I$2-A2+1,IF(IF(AND(A2<h$2,b2>I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E2)+(IF(IF(AND(A3>=H$2,B3<=I$2),TRUE,FALSE)=TRUE,B3-A3+1,IF(IF(AND(A3<h$2,b3<=i$2),true,false)=true,b3-h$2+1,if(if(and(a3>=H$2,A3<=I$2,B3>I$2),TRUE,FALSE)=TRUE,I$2-A3+1,IF(IF(AND(A3<h$2,b3>I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E3)+(IF(IF(AND(A4>=H$2,B4<=I$2),TRUE,FALSE)=TRUE,B4-A4+1,IF(IF(AND(A4<h$2,b4<=i$2),true,false)=true,b4-h$2+1,if(if(and(a4>=H$2,A4<=I$2,B4>I$2),TRUE,FALSE)=TRUE,I$2-A4+1,IF(IF(AND(A4<h$2,b4>I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E4)</h$2,b4></h$2,b4<=i$2),true,false)=true,b4-h$2+1,if(if(and(a4></h$2,b3></h$2,b3<=i$2),true,false)=true,b3-h$2+1,if(if(and(a3></h$2,b2></h$2,b2<=i$2),true,false)=true,b2-h$2+1,if(if(and(a2>

Slightly incorrect formula above. I can't post the correct one for some reason... it's too long.

 

[FormR]

Hi, here is an option that you can try:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Start Date	Finish Date	Effective Days	Total Amount	Daily Amount		Month	Start of Month	End of Month	Monthly Amount
2	04/09/2019	22/10/2019	49	1000	20.41		01/09/2019	01/09/2019	30/09/2019	1325.31
3	15/09/2019	15/10/2019	31	1500	48.39		01/10/2019	01/10/2019	31/10/2019	1904.3
4	01/10/2019	24/12/2019	85	2000	23.53		01/11/2019	01/11/2019	30/11/2019	705.9
5							01/12/2010	01/12/2019	31/12/2019	564.72
Sheet1

Cell Formulas
Range	Formula
J2	{=SUM(IF($B$2:$B$4>=H2,IF($A$2:$A$4<=I2,$E$2:$E$4*(1+IF($B$2:$B$4>=I2,I2,$B$2:$B$4)-IF($A$2:$A$4<=H2,H2,$A$2:$A$4)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[PuGZoR]

Hi, here is an option that you can try:

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J
Start Date	Finish Date	Effective Days	Total Amount	Daily Amount	Month	Start of Month	End of Month	Monthly Amount

<colgroup><col style="width: 25pxpx"><col><col><col><col><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"]04/09/2019[/TD]
[TD="align: right"]22/10/2019[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20.41[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/09/2019[/TD]
[TD="align: right"]01/09/2019[/TD]
[TD="align: right"]30/09/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1325.31[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15/09/2019[/TD]
[TD="align: right"]15/10/2019[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]48.39[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/10/2019[/TD]
[TD="align: right"]01/10/2019[/TD]
[TD="align: right"]31/10/2019[/TD]
[TD="align: right"]1904.3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/10/2019[/TD]
[TD="align: right"]24/12/2019[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]23.53[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/11/2019[/TD]
[TD="align: right"]01/11/2019[/TD]
[TD="align: right"]30/11/2019[/TD]
[TD="align: right"]705.9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/12/2010[/TD]
[TD="align: right"]01/12/2019[/TD]
[TD="align: right"]31/12/2019[/TD]
[TD="align: right"]564.72[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]{=SUM(IF($B$2:$B$4>=H2,IF($A$2:$A$4<=I2,$E$2:$E$4*(1+IF($B$2:$B$4>=I2,I2,$B$2:$B$4)-IF($A$2:$A$4<=H2,H2,$A$2:$A$4)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Sir, you are a gentleman and a scholar. This worked BEAUTIFULLY. Thank you so much! If you're ever around Brisbane, Australia, I would very much like to buy you a beverage.