Service charge calls in Excel: which unit still has a remainder
A service charge call list is one row per unit for one period word. You type the unit, the owner, the period, the amount called, and the amount received. The gap is called minus received while the row is open. The window says short when that gap is above zero, clear when the amounts match, and over when received is above the call. The first screen is a sheet named Calls. Row 1 is the header. Row 2 is one unit you can check by hand: called 450, received 300, status open. The gap is 150. The window is short. There is no lease date and no invoice number on that screen. This sheet is not a lease renewal calendar. That calendar uses an end date and notice days. This list uses the two amounts you typed for a call you already sent. It is not a chase of trade invoices, and it is not a list of apartments for sale. The period is a word you type so two calls on the same unit stay on two rows. You type both amounts. The question is which open row still has a remainder. Closed rows drop out of the window. They stay on the sheet. A negative gap is over, not a number to hide. Elyx can write the gap and the window. You still type the call and the receipt. The trial lasts 7 days, with no card.
The Problem
The call fails when it lives in a letter. A sentence says unit A12 still owes. Nobody can add the remainders, because the called amount and the received amount are not on the same row. A sentence without those two numbers is not a call list. A second failure is mixing this sheet with a lease calendar. A lease row answers which end date is inside the notice. A collection row answers which customer invoice is late. A sales row answers which apartment is still offered. If you paste lease dates, trade invoices, or listings here, the morning sum no longer means the remainder of the calls you typed. Keep this sheet to units, period words, and the two amounts. A third failure is deleting a call when the money arrives. Set the status to closed and leave the row. The gap goes blank. Deleting the row removes the only record of the 450 and the 300 you already typed. The sheet still does not decide what the call should have been.
Benefits
Each call is one row with a unit, a period, a called amount, and a received amount you typed.
The gap is called minus received while the row is open.
Short means the gap is above zero. Clear means the amounts match. Over means received is above the call.
Closed rows stay on the sheet and drop out of the short sum.
The period is a label. It is not a due date.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Calls. In row 1 type: Lot, Owner, Period, Called, Received, Status, Gap, Window, Note. Lot is the unit. Owner is the name you use. Period is a word you type, such as Q1. Called and Received are amounts you typed, not a formula. Gap and Window are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.
One row per unit and period. A second call on the same unit is a second row. Do not pack two periods into one cell.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and closed. Open means the remainder is still yours to follow. Closed means you are done with that call. Only open stays in the window. People must pick a word. A synonym such as paid or done will not match the formulas. The list is a filter. It is not a statement about a lease.
Called and Received are numbers. Do not type the word short in those columns.
Enter five calls you can check by hand
On row 2 type lot A12, period Q1, called 450, received 300, status open. You will predict gap 150 and window short. On row 3 type lot B07, period Q1, called 200, received 80, status open. Gap should be 120 and window short. On row 4 type lot C02, called 450, received 450, status open. Gap should be 0 and window clear. On row 5 type lot D18, called 450, received 500, status open. Gap should be -50 and window over. On row 6 type lot E09, called 450, received 300, status closed. Gap and window should go blank. Use these amounts before you trust the formulas.
If you do not know the received amount yet, leave Received blank. The gap stays blank. Do not type zero unless you really received zero.
Write the gap
The gap is blank when status is closed, or when Called or Received is blank. Otherwise it is Called minus Received. With Called in D2, Received in E2, and Status in F2, use =IF(OR(F2="closed",D2="",E2=""),"",D2-E2). Format as a number. A positive number is still to collect. Zero means the amounts match. A negative number means received is above the call. That is a signal to open the row. It is not a new charge.
=IF(OR(F2="closed",D2="",E2=""),"",D2-E2)450 minus 300 is 150. 450 minus 500 is -50. If you see another number, the cells are not the amounts you typed.
Write the window
The window is blank when status is closed, or when either amount is blank. Otherwise it is short when Called minus Received is above zero, over when that difference is below zero, and clear when it is zero. Use =IF(OR(F2="closed",D2="",E2=""),"",IF(D2-E2>0,"short",IF(D2-E2<0,"over","clear"))). Short means a remainder. Clear means the two amounts match. Over means the receipt is above the call. The owner and the period are not in this formula.
=IF(OR(F2="closed",D2="",E2=""),"",IF(D2-E2>0,"short",IF(D2-E2<0,"over","clear")))Do not wrap the gap in ABS. Hiding the minus sign hides the row where received is 500 against a call of 450.
Count and add the short rows
On a sheet named Morning, the short rows are =COUNTIFS(Calls!H:H,"short"). With the five rows above, that count is 2: the gap of 150 and the gap of 120. The short sum is =SUMIFS(Calls!G:G,Calls!H:H,"short"), which is 270. The over count is =COUNTIFS(Calls!H:H,"over"), which is 1, the gap of -50. Closed row E09 is out because its window is blank. A blank window is not short. Write the rule next to the count.
=SUMIFS(Calls!G:G,Calls!H:H,"short")Do not add the -50 into the short sum. Over is a different word, so SUMIFS leaves it out.
Read a negative gap as a signal, not as a deletion
If the gap is negative and status is still open, received is above the call and the row was not closed. That is a row to open, not a row to delete. Either the receipt was typed on the wrong unit, or the call amount is short of what you meant. Fix the cell that is wrong. The -50 on D18 does not say the unit owes a new charge. It says 500 was typed against a call of 450. A gap on a closed row should not happen, because closed blanks the cell. If you see a number on a closed row, the formula was overwritten.
Filter Window to short for the morning list. Filter Period to Q1 when you only want that word. Filter Status to closed when you need history.
Check the five rows before you share the sum
Called 450 and received 300, status open: gap 150, window short. Called 200 and received 80, status open: gap 120, window short. Called 450 and received 450, status open: gap 0, window clear. Called 450 and received 500, status open: gap -50, window over. The same 450 and 300 with status closed: gap blank, window blank, and the short sum ignores the row. The short sum of the open rows is 270, and the short count is 2. If a check fails, fix the amounts or the status word before you send the sum. The sum is only this sheet. It is not a lease date, and it is not a trade invoice.
Write the five checks on Morning if more than one person updates the calls.
Template Features
Gap while the call is open
Called minus received. Blank when status is closed or an amount is empty.
=IF(OR(F2="closed",D2="",E2=""),"",D2-E2)Window from that gap
Short when the gap is above zero, over when it is below zero, clear when it is zero.
=IF(OR(F2="closed",D2="",E2=""),"",IF(D2-E2>0,"short",IF(D2-E2<0,"over","clear")))Short count
COUNTIFS reads the window word. With the sample rows the count is 2.
=COUNTIFS(Calls!H:H,"short")Short sum
SUMIFS adds only gaps whose window is short. The sample sum is 270.
=SUMIFS(Calls!G:G,Calls!H:H,"short")History stays on the sheet
Closed rows remain. They leave the short sum because the window is blank.
Concrete Examples
A remainder of 150
Unit A12 was called 450 for period Q1 and 300 has been received. You want it on the morning list.
Called: 450. Received: 300. Status: open.
Result: Gap is 150. Window is short. The short count includes the row. The 450 and the 300 are the numbers you typed.
A second short row changes the sum
Unit B07 was called 200 and 80 has been received. A12 is still short by 150.
B07 called 200, received 80, status open. A12 still 450 and 300.
Result: B07 gap is 120, window short. The short sum is 270, which is 150 plus 120. The short count is 2.
A receipt above the call
Unit D18 was called 450 and 500 was typed as received. You do not want that row in the short sum.
Called: 450. Received: 500. Status: open.
Result: Gap is -50. Window is over. The short sum stays 270. Closing the row blanks the gap and leaves the amounts on the sheet.
Pro Tips
Zero received is a number
Type 0 only when nothing has been received. A blank cell means you do not know yet, and the gap stays blank. 450 minus 0 is a gap of 450 and a window of short.
Period is a label
Q1 lets you filter. The window formula never reads the period. A second period on the same unit is another row, with its own called and received amounts.
Text amounts do not subtract
If Called was pasted as text, D2-E2 will not be 150. Re-enter 450 and 300 as numbers. Then read the window again.
Distinct from a lease calendar
Lease end dates belong on the property manager calendar. Trade invoices belong on the collection list. Apartments for sale belong on the agent list. Do not paste those rows here and expect this sum to mean the same thing.