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

PuGZoR

New Member
Joined
Apr 3, 2014
Messages
7
Hi there,

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

Start DateFinish DateEffective DaysTotal AmountDaily AmountMonthStart of MonthEnd of MonthMonthly Amount
04/09/201922/10/201949100020.41Sep-1901/09/201930/09/2019???
15/09/201915/10/201931150048.39Oct-1901/10/201931/10/2019???
01/10/201924/12/201985200023.53Nov-1901/11/201930/11/2019???
Dec-1001/12/201931/12/2019???

<tbody>
</tbody>

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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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).
 
Upvote 0
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>
 
Upvote 0
=(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. :(
 
Last edited:
Upvote 0
Hi, here is an option that you can try:


Excel 2013/2016
ABCDEFGHIJ
1Start DateFinish DateEffective DaysTotal AmountDaily AmountMonthStart of MonthEnd of MonthMonthly Amount
204/09/201922/10/201949100020.4101/09/201901/09/201930/09/20191325.31
315/09/201915/10/201931150048.3901/10/201901/10/201931/10/20191904.3
401/10/201924/12/201985200023.5301/11/201901/11/201930/11/2019705.9
501/12/201001/12/201931/12/2019564.72
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
Hi, here is an option that you can try:

Excel 2013/2016
ABCDEFGHIJ
1Start DateFinish DateEffective DaysTotal AmountDaily AmountMonthStart of MonthEnd of MonthMonthly Amount
204/09/201922/10/201949100020.4101/09/201901/09/201930/09/20191325.31
315/09/201915/10/201931150048.3901/10/201901/10/201931/10/20191904.3
401/10/201924/12/201985200023.5301/11/201901/11/201930/11/2019705.9
501/12/201001/12/201931/12/2019564.72

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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)))))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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