SUM on specific dates

[Miron]

I have a list of items. Each item got a date range and a sum amount.
Created a list of dates and trying to sum all items on specific dates.
I know its supposed to be simple but the solution evades me.

Sum Dates.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2
3	Asset	Start	End	Total Period	Payment each month		Interests	Start Period	End Period	% Year	% Month
4	Office	01/10/2020	01/11/2020	1	63,500.00		Monthly	01/10/2020	30/09/2024	3.56%	0.29%
5	Office	01/11/2020	01/10/2021	11	65,000.00
6	Office	01/10/2021	01/10/2022	12	70,000.00
7	Office	01/10/2022	01/10/2023	12	75,000.00		Earliest	01/10/2020
8	Office	01/10/2023	01/10/2024	12	77,500.00		Latest	01/10/2024
9	Parking	01/10/2020	01/10/2024	48	7,920.00		Length Months	48
10
11
12
13	Date	Sum per month				Manual calculation :(
14	01/10/2020	71,420	Numbers I trying to calculate with formula and not manualy			=F4+F9
15	01/11/2020	72,920	Numbers I trying to calculate with formula and not manualy			=F5+F9
16	01/12/2020
17	01/01/2021
18	01/02/2021
19	01/03/2021
20	01/04/2021
21	01/05/2021
Sheet1

Cell Formulas
Range	Formula
L4	L4	=(1+[@[% Year]])^(1/12)-1
E4:E9	E4	=ROUNDDOWN((D4-C4)/30,0)
I7	I7	=MIN(Table1[Start],Table1[End])
I8	I8	=MAX(Table1[Start],Table1[End])
I9	I9	=ROUNDDOWN((I8-I7)/30,0)
B14:B61	B14	=EOMONTH(I7,SEQUENCE(I9)-2)+1
C14	C14	=F4+F9
C15	C15	=F5+F9
G14:G15	G14	=FORMULATEXT(C14)
Dynamic array formulas.

 

[jasonb75]

This is what you have asked for but I have my suspicions that it may not be exactly what you need.

The End column in Table1 is a bit misleading, based on your manual calculations it appears that the actual end date is the day before the one shown. I've allowed for this in the formula but there may be occasions when it is not correct.

=SUMIFS(Table1[Payment each month],Table1[Start],"<="&B14#,Table1[End],">"&B14#)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For cell C14 try

SUMIFS(Table1[Payment each month],Table1[Start],"<="&B14,Table1[End],">"&B14)