Problem with a formula

[Ptnana]

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?

[#2]

@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.

[#3]

@Ptnana 

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.

[#4]

Thank you for your reply. Unfortunately, I am not familiar with the process of sharing to the venues you suggested. If you can give me the information on how to do this, I would appreciate the assistance.

The program has 40+ rental units, which I have on one section of the document. On the second section is the tenant payment information. the next 12 sections are monthly reports for each tenant showing payment dates, past due amounts if any and any comments needed for each tenant. My customer wants a report each month showing how much was paid and how much each tenant is behind in payment. As it is currently set up, the payments for January will add the monthly amount due for February, March and April. This does not look good if I have to go back and reprint these reports for whatever reason.

[#5]

Thank you for your reply.

The program has 40+ rental units, which I have on one section of the document. On the second section is the tenant payment information. the next 12 sections are monthly reports for each tenant showing payment dates, past due amounts if any and any comments needed for each tenant. My customer wants a report each month showing how much was paid and how much each tenant is behind in payment. As it is currently set up, the payments for January will add the monthly amount due for February, March and April. This does not look good if I have to go back and reprint these reports for whatever reason.

The program has 40+ rental units, which I have on one section of the document. On the second section is the tenant payment information. the next 12 sections are monthly reports for each tenant showing payment dates, past due amounts if any and any comments needed for each tenant. My customer wants a report each month showing how much was paid and how much each tenant is behind in payment. As it is currently set up, the payments for January will add the monthly amount due for February, March and April. This does not look good if I have to go back and reprint these reports for whatever reason.

[#6]

By the way, please use email address removed for privacy reasons in your reply. I don't use the MSN email account.