1

I have a rent ledger sheet that lists all the rent due dates (Column A) and amounts (Columns B & C) both past and future as shown below.

Each month there are two payments due, Column B is due on the 1st of each month, and Column C is due on the 15th of each month.

At the bottom, I have a running total of accumulated rent due from the beginning of the lease through what is then due, but I have to always manually update it according to the current date because otherwise, it would total the future amounts as well that are not due yet.

What I need is a formula that would automatically add up these two columns, but based on the then current date.

So for example, if it was August 21, it would add up all the rent due in both columns through August 2023 (because both due dates have passed) but if it was earlier on August 14, while it would still add up all rent from Column B through August because the due date has passed, it would only add up the rent from Column C through July, because the due date has not passed.

Essentially, it would use the current date to determine what the total rent due was from the beginning of the sheet through the appropriate column.

The first column can be handled by this SUMIF formula: SUMIF(A1:A8,"<="&TODAY(),B1:B8) because it compares the entire date in Column A to TODAY, but I have no idea how to do the second column, where it needs to compare the DAY(TODAY) to the DAY(A) in Column A?

May 2023 $2,400.00 $850.00
June 2023 $2,400.00 $850.00
July 2023 $2,400.00 $850.00
August 2023 $2,400.00 $850.00
September 2023 $2,400.00 $850.00
October 2023 $2,400.00 $850.00
November 2023 $2,400.00 $850.00
December 2023 $2,400.00 $850.00
1
  • What exactly are the dates you put in Column A? Are they all the first of the respective months or is it random as to the day of the month?
    – Alper
    Commented Aug 23, 2023 at 23:33

1 Answer 1

1

Just subtract from your TODAY() function:

SUMIF(A1:A8,"<="&TODAY()-14,C1:C8)
1
  • Thanks a million! Works like a charm. Commented Aug 24, 2023 at 16:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .