Need Help With Formula For Rent Ledger

No need to make it complicated:

In B13: =SUMIF($A1:$A12,"<="&TODAY(),B1:B12)

In C13: =SUMIF($A1:$A12,"<="&TODAY()−14,C1:C12)

or if you don't want to have the subtotals, then a single formula:

= SUMIF($A1:$A12,"<="&TODAY(),B1:B12)+SUMIF($A1:$A12,"<="&TODAY()−14,C1:C12)

To consider the amount in column C just subtract 14 days to today's date to compare to the date in the first column (which is the first of the month even though only the month name is shown, unless you specifically entered another day.).

If today is the 14th, then the subtraction results in the last day of the past month, so we don't add the amount for this month in column C. If we're on the 15th or after, then the subtraction gives a result equal to or superior to the first of the month.

The way the date is displayed has no bearing on the calculation, it's just window-dressing. However when you enter a date you have to make sure you set the parts in the proper order.

Hi mac_user_4ever,

mac_user_4ever wrote:

my boss doesn't much care for extra sheets or hidden helper columns, for whatever reason.

I sympathise with you. You have a difficult assignment.

I tried every way I know to find a solution that fits the constraints set by your boss. I was not able to find a way to sum two columns without using helper columns that can then be hidden.

The problem I see is that column A does not distinguish between days of the month (day 1 and day 15).

Here is my suggestion. Use helper columns, hide them and then export to PDF. No-one can look "behind the curtain" in a PDF. PDF is a final presentation.

Here we go. Two helper columns, D and E.

Using the DATE function,

Formula in D2: DATE(TEXTAFTER($A2," "),MONTH(TEXTBEFORE($A2," ")),B$1)

Fill down and fill right.

Now for the SUMIF formulas.

Formula in Footer Cell B11: SUMIF(D,"<="&TODAY(),B)

Fill right to C11.

Hide columns D and E

Export to PDF and open in Preview

That is your presentation to your overbearing boss.

Good luck, or look up Johnny Paycheck: Take this job and shove it.

https://www.youtube.com/watch?v=eIjEauGiRLo

Skip the Ads.

Regards,

Ian.

OK, using that layout

Column A is formatted as Date & Time, displaying only the month and year. The actual date is the 1st day of that month (1/5/2023 as Day/Month/Year in my region).

Formula in Footer cell B11: SUMIF($A,"<="&TODAY(),B)

Fill Right to C11

Formula in Footer cell B12: B$11+C$11

Regards,

Ian.

Hi mac_user_4ever,

Forget the earlier formula.

SUMIF($A,"<="&TODAY(),C) (my version)

SUMIF(A2:A8,"<="&TODAY(),C2:C8) (your version)

That is only partly useful. As you correctly pointed out, it uses the first day of the month; if TODAY is before the 15th, it includes the 15th before it is due.

Using my latest solution, changed temporarily so that we can put in a pretend TODAY, just as a test.

Formula in B11: SUMIF(D,"<="&Pretend TODAY::$A1,B)

Fill right.

That is correct, because 25 August 2023 is later than the 15th day.

Column B SUMIF $9,600.00

Column C SUMIF $3,400.00

Using a pretend TODAY of 2 august 2023, the formula will include 1st August, but not 15th August.

Column B SUMIF $9,600.00

Column C SUMIF $2,550.00

Regards,

Ian.

Hi mac_user_4ever,

I strongly suggest that you place all dates in one column and all amounts in another column. That will simplify the calculation. Here is a table with one Header Row.

Formula in C2: SUM(B$1:B2)

Fill down.

We can add a column to identify dates before today:

Formula in D2: IF(A2<=TODAY(),"Past","Future")

Regards,

Ian.

Hi mac_user_4ever

Do not be constrained by a template.

You wrote:

"Unfortunately I am constrained to use the template I was given that lists the dates and rent on the same single route with the values as shown."

Fortunately, in Numbers we can do calculations "behind the curtain" on other Sheets (Tabs) and then create a final presentation that matches the required table.

It is late here and I must go to sleep. More tomorrow, and of course other users are welcome to reply.

Regards,

Ian.