ElyxAI

Customer collections in Excel: the promise and the next call

Credit ControllerCollection Follow-up

A collections sheet is the call list. One row is one customer invoice that is still open. You type the customer, the invoice number, the amount, the due date on the invoice, the promise date the customer gave you, and the next date you intend to act. Status is open or paid. SUMIF totals the open amount for one customer. The days past due are today minus the invoice due date while the row is open. This is not a bank match, and it is not the list of supplier bills you have to pay. The amount on the row is what the customer still owes on that invoice. The promise date is what they said, which may be later than the invoice due date. Both dates stay. Replacing the due date with the promise makes the lateness disappear. The sheet does not know a late fee, a legal rate, or a collection agency. If you charge a fee, you type that amount in its own column. The formulas add the days and the totals you already have. They do not invent a percentage. A row is ready for today's calls when status is open and the next action date is today or earlier. COUNTIFS counts those rows. Paid rows stay on the sheet so you can see what was collected. They drop out of the open total because SUMIF asks for the status open. Before you call, read three invoices against the ledger or the invoice itself: the amount, the due date, and the customer name. If those three match, the total is the total of this list. Elyx can write the day count and the customer total from the invoices you imported. You still write down what the customer said. The trial lasts 7 days, with no card.

The Problem

Collections drift when the promise lives in a notebook and the invoice lives in a sheet. The next person sees an overdue invoice and calls again, or waits because they heard that someone else "had a conversation". The promise date and the next action date have to be on the row. A second drift is marking an invoice paid because the customer promised. Paid means the money is recorded, not that a date was offered. Leave status open, type the promise date, and set the next action to the day you will check whether the money arrived. If you mark it paid early, SUMIF drops the amount and the call list goes quiet while the cash is still missing. A third drift is one row per customer with a single balance you retype. You then cannot see which invoice was promised, and a part payment has nowhere to go. One row per invoice. A part payment is a new amount you type on that invoice, with a note of the date and the sum received. The open amount is what is still left, typed by you, not a balance the sheet fetches from a bank.

Benefits

One open invoice per row, with the due date and the promise date kept separate.

Days past due are today minus the invoice due date while status is open.

SUMIF totals the open amount by customer and ignores paid rows.

COUNTIFS counts rows whose next action date is due.

A promise does not mark the invoice paid.

Step-by-Step Tutorial

1

Set up the invoice rows

Name the sheet Calls. Headers: Customer, Invoice, Amount, Due, Promise, LastAction, NextAction, Status, Days, Note. Customer and Invoice identify the debt. Amount is what is still open on that invoice, in the currency you write in the header. Due is the invoice due date. Promise is blank until the customer gives you a date. LastAction is the date you last called or wrote. NextAction is the date you choose to act again. Status is open or paid. Days is a formula. Note is what was said, in one or two sentences.

Repeat the customer name exactly, or pick it from a list. SUMIF will split one customer into two if the spelling differs.

2

Keep paid rows and exclude them from the total

Validate Status as open or paid. When the cash is recorded against that invoice, set Status to paid. Do not delete the row. The open total for a customer in cell L2 is =SUMIF(A:A,L2,C:C) only if every row for that customer is still open. Once paid rows exist, use =SUMIFS(C:C,A:A,L2,H:H,"open"). The second test is the status. A paid invoice can stay visible for the story of the account and stay out of the number you chase.

=SUMIFS(C:C,A:A,L2,H:H,"open")

Label the cell next to the total with the word open, so nobody adds the paid rows back by hand.

3

Count the days past the invoice due date

Days past due should be blank on a paid row, blank if the due date is empty, and a day count otherwise. =IF(OR(H2="paid",D2=""),"",TODAY()-D2) with Due in D and Status in H. A negative number means the invoice is not yet due. Zero means it is due today. A positive number means it is late. Do not use the promise date in this formula. The promise answers when they said they would pay. The day count answers how late the invoice is against its own due date. Both can be true at once.

=IF(OR(H2="paid",D2=""),"",TODAY()-D2)

Format Days as a number. If you see a date, the cell format is wrong. The value is a count of days.

4

Write the promise without erasing the due date

When the customer names a day, type it in Promise. Leave Due as it was. Set NextAction to the promise date, or to the morning after, if that is when you will check the bank. Type today's date in LastAction and a short Note: what they said, and who on your side heard it. Do not change Amount because of a promise. Amount changes when you record money or a credit you have actually agreed, and the note should say which.

If they give a promise and then miss it, do not delete the old promise. Put the new date in Promise and mention the missed date in Note.

5

List who is due for a call today

A row belongs on today's list when Status is open and NextAction is today or earlier. =COUNTIFS(H:H,"open",G:G,"<="&TODAY()) counts them. To see the rows, filter Status to open and filter NextAction to dates on or before today. Sort by Days from largest to smallest if you want the latest invoices first, or sort by Amount if you want the largest balances first. Pick one sort for the session and leave it. Calling the oldest and the largest at once is a decision you make by reading, not a second formula you need on day one.

=COUNTIFS(H:H,"open",G:G,"<="&TODAY())

An empty NextAction does not appear in this count. Every open invoice needs a next date, even if the date is today.

6

Record the call, then move the next date

After the call, type today in LastAction. If they made a new promise, update Promise and set NextAction to that date. If they did not answer, set NextAction to the next day you will try, and write no answer in Note. If they paid and you have recorded the cash, set Status to paid and you can leave NextAction as it is: the count ignores paid rows. Do not leave NextAction in the past on an open invoice unless you still intend to act today. A past next date means the call is still to do.

Note should stay short. A full email can live in your mail. The sheet needs the date and the outcome.

7

Handle a part payment on the same invoice

If part of the invoice is paid, replace Amount with what is still open and write the received sum and the date in Note. Status stays open. Days still run from the original due date, which is what you want: the remainder is still late if the due date has passed. Do not split one invoice into two rows unless you really issued two documents. SUMIFS will add every open row for that customer, so a second row with the same invoice number would double-count unless you reduced the first.

If you want the original amount kept, put it in a column you do not sum. The column you sum must be the open remainder only.

8

Check three invoices before the call block

Choose three open rows. The customer and the invoice number must match the document. The amount must match what is still open, including any part payment you already typed. The due date must match the invoice, not the promise. Days must equal today minus that due date. If one row is wrong, fix it before you tell anyone the customer total. SUMIFS only adds the cells. It does not know which invoice is real.

Aging buckets, if you use them, group amounts by how late they are. This sheet is the next action. You can keep both. Do not replace this list with the buckets.

Template Features

Open amount by customer

SUMIFS adds Amount where the customer matches and the status is open. Paid rows stay on the sheet and stay out of the total.

=SUMIFS(C:C,A:A,L2,H:H,"open")

Days past the invoice date

Today minus the due date, blank when the row is paid. The promise date is not part of this count.

=IF(OR(H2="paid",D2=""),"",TODAY()-D2)

Today's call count

COUNTIFS counts open rows whose next action date is today or earlier.

=COUNTIFS(H:H,"open",G:G,"<="&TODAY())

Promise kept separate

Promise is an entry. It never overwrites Due and it never sets Status to paid.

Status list

Data validation limits Status to open or paid so the total and the call count see the same words.

Concrete Examples

One overdue invoice and a promise

An invoice was due 10 days ago. The customer says they will pay in 5 days. You want the lateness and the promise both visible.

Amount: 1200. Due: 10 days before today. Promise: 5 days after today. Status: open. NextAction: the promise date.

Result: Days is 10. Status stays open, so the 1200 stays in the customer total. The row leaves today's call count until the next action date arrives, because that date is in the future. The due date is unchanged.

A promise that is not cash

Someone marked the invoice paid after a phone call. The money is not in the ledger.

You set Status back to open, type the promise date, and set NextAction to the day you will check.

Result: SUMIFS includes the amount again. Days shows the lateness against the invoice due date. The note says the earlier paid mark was a promise, not cash.

A part payment

The open amount was 1200. You record 400 received. 800 is still open.

Amount changed from 1200 to 800. Status: open. Note: 400 received, and the date.

Result: The customer total uses 800. Days still uses the original due date. You did not add a second row, so the invoice is not counted twice.

Pro Tips

Currency in the header

If every amount is in one currency, write it in the header. SUMIFS will add numbers regardless. Do not mix currencies in the Amount column and then read the total as if it were one currency.

Next action on every open row

An open invoice with a blank NextAction never joins today's count. When you import new invoices, set NextAction to today or to the due date, on purpose, in one fill.

Customer names from a list

Validation or a lookup from a customer sheet stops the spelling splits. If you correct a name, correct every open row for that customer or the total splits.

Do not store a fee the sheet invented

A penalty you are allowed to charge is an amount you type after you decide to charge it. A formula that multiplies days by a rate you copied from memory will be treated as a real fee. Leave it out until the amount is yours.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Customer collection follow-up, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions