Accounting to reflect the balance due

Question

In the above example, I am trying to determine the balance due for a tenant who makes partial payments every month. As you can see, Column I reflects what should be the balance and the first row is correct. However, the second row is not correct because it does not consider row 1's balance.

I want the balance due reflected for this one tenant and also keep track of other tenants in the same log. If this were SQL, it would be rather easy. Just:

 DECLARE AmountPaid int = (SELECT SUM(AmountPaid) FROM table WHERE tenantname = @TenantName)
 DECLARE Rent int = (SELECT SUM(MonthlyRent) FROM table WHERE tenantname = @TenantName)

 RETURN MonthlyRent - AmountPaid

In any case, I am looking to do this in Excel sans macros and sans VB. Inline is preferred, but I am prepared for the answer to this to be "can't be done like that".

Answer 1

Three options for you.

First Option

This requires that the rent be put in only once per month no matter how many transaction per month. Put the following formula in I2 and copy down:

=SUMIF($A$1:$A2,A2,$C$1:$C2)-SUMIF($A$1:$A2,A2,$F$1:$F2)+SUMIF($A$1:$A2,A2,$G$1:$G2)-SUMIF($A$1:$A2,A2,$H$1:$H2)

Second Option

If you want to have the reference of the monthly rent on every line you will need a helper column. As I stated in the comments, Excel can not guess whether to use a number or not. So add column between C and D for a Y or N. The formula would then be:

=SUMIFS($C$1:$C2,$A$1:$A2,A2,$D$1:$D2,"Y")-SUMIF($A$1:$A2,A2,$G$1:$G2)+SUMIF($A$1:$A2,A2,$H$1:$H2)-SUMIF($A$1:$A2,A2,$I$1:$I2)

Third Option My Choice

One last argument for one line per transaction.

Rent is due on the first no matter when the rent is paid and so on the first the balance should reflect that. Late fees have a specific date that it is accrued regardless of date of payment and should be added to the balance as of that date.

With the sumif formulas above we can keep it all on one sheet. The formula would be:

=SUMIF($A$1:$A2,A2,$E$1:$E2)-SUMIF($A$1:$A2,A2,$D$1:$D2)