Excel Date Sum Total

[charcoop14]

Hello all,
I need to create two formulas to bring back the sum of total for two columns based on today's date. Below I have 5 properties, cell A1 has function =(today). How do I have total commitment column give me value of all values from today and in the past and remaining retrieve the rent that is still left based on todays date. Thank you all!

11/18/2021​
	1-Nov	1-Dec	Total 2021	1-Jan	1-Feb	Total 2021	Total Commitment	Remaining
Property A	$ 5,000	$ 5,000	$ 10,000	$ 5,000	$ 5,000	$ 10,000
Property B	$ 6,000	$ 6,000	$ 12,000	$ 6,000	$ 6,000	$ 12,000
Property C	$ 10,000	$ 10,000	$ 20,000	$ 10,000		$ 10,000
Property D	$ 23,500	$ 23,500	$ 47,000	$ 23,500		$ 23,500
Property E	$ 25,000	$ 25,000	$ 50,000	$ 25,000		$ 25,000

 

[mackc557]

Just to clarify what you mean about Remaining column; are you looking to get a full month's amount in remaining or are you looking for a per-diem prorate or something different?

Also I take it that there will be more dates before and after these. As well I assume you meant for G2 to say Total 2022.

 

[charcoop14]

Just to clarify what you mean about Remaining column; are you looking to get a full month's amount in remaining or are you looking for a per-diem prorate or something different?

Also I take it that there will be more dates before and after these. As well I assume you meant for G2 to say Total 2022.

yes sorry about that. full months rent, not prorated.

yes will be 5 years ahead and yes, should be total 2022

 

[mackc557]

Go ahead and try these formulas, they should work IF:
$A$1 is your =TODAY()
Row 2 is your date range

Book2
	H	I
2	Total Commitment	Remaining
3	$ 5,000.00	$ 5,000.00
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUMIF(INDIRECT("$A$2:"&ADDRESS(2,LEN(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))))-LEN(SUBSTITUTE(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))),"`","")))),"<="&$A$1,INDIRECT(ADDRESS(ROW(),1)&":"&ADDRESS(ROW(),LEN(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))))-LEN(SUBSTITUTE(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))),"`","")))))
I3	I3	=IF(DAY($A$1)>1,SUMIF(INDIRECT("$A$2:"&ADDRESS(2,LEN(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))))-LEN(SUBSTITUTE(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))),"`","")))),DATEVALUE(MONTH($A$1) & "/1/" & YEAR($A$1)),INDIRECT(ADDRESS(ROW(),1)&":"&ADDRESS(ROW(),LEN(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))))-LEN(SUBSTITUTE(LEFT(TEXTJOIN("`",FALSE,$2:$2),FIND("``````````",TEXTJOIN("`",FALSE,$2:$2))),"`",""))))),0)