May 30 2022 11:07 PM
I have a spreadsheet in which I post rent amounts for each month. the spread sheet consists of 12 windows, one for each month, A base sheet which contains names and amounts and a record sheet with approximately 40 names containing information on payments for each month of the year. the formula I am trying to achieve is the monthly balance owed for each month.
The Payment Statement record (Tenant Payment Record) is basically as follows beginning with January as line 1:
Column D1 is the monthly amount due,
Column E1 is any miscellaneous amount past due or over paid
Column G1 is the amount paid
Column H1 is the balance unpaid or over paid
Column H13 is the balance due for the following Month
my formula for the monthly summary report is :
Column D is the amount paid for the month
Column F should be the amount due for that month
(Tenant Payment Record H12 minus Tenant Payment Record D2)
However, as each month progresses, column D keeps adding up in January, giving a false representation.
What I want to do is list only the amount owed for each month. In other words, the formula should equal to (Tenant Payment Record H12 minus the sum of Tenant Payment Records D2-D12) for January.
Can someone give me an idea as to how this equation should be worded?
May 31 2022 01:44 AM
@Ptnana Difficult to visualize your the sheets you are working on. Can you share the file (Onedrive, Google docs etc). Remove any private and confidential information, though.
My gut feeling, however, says that you should keep track of all transactions in one sheet. Just be sure to add a transaction date to each entry and let Excel's built-in date and time intelligence do the summarizing per month do for you.
May 31 2022 02:01 AM
In general terms, there are two approaches to such a problem. One, more typical of database applications is to sum everything but to use SUMIFS which allows the criterion fields to determine whether to include each particular value in the total.
The other, more array oriented, is to identify the values to be summed as a contiguous range of cells (might involve searching for the first and last occurrence of "January" using XLOOKUP, for example) and apply the SUM function to the restricted range. We would need more information to determine which will serve your needs the better.
May 31 2022 05:41 PM
May 31 2022 05:43 PM
May 31 2022 05:52 PM