Tracking Multiple Payments & Multiple Invoices in Excel

In this week's episode, we will learn how to write formulas to allocate multiple payments made against the same invoice or multiple payments made by customers across multiple invoices.

We will use the invoice tracker invoice template that is available for free to download from indzara.com.
https://indzara.com/2016/07/invoice-tracker-template-free/

For an Excel Template with features such as automatic payment allocation against open invoices, Dashboard, Unpaid Invoices Report and Customer Report, watch Invoice Manager Excel Template video:
https://youtu.be/oE2CM6CRSc8
Download: https://indzara.com/product/invoice-manager-excel-template/

In this template, each invoice is entered in a separate row, and then the customer for that each invoice is named here, and then each invoice will have an invoice date and a due date and the invoice amount. And then the paid amount is the amount that has been paid so far by the customer, and that is being entered directly in this column, and in the outstanding amount and what's the status and everything will be calculated by the template using formulas automatically.

Scenario 1: Customer makes multiple payments towards a single invoice:

If you want to track the customer who is making multiple payments for each invoice, then you will set up a table like this with just three columns, invoice number, payment date, and amount that the customer paid for that.

Now, we want to make sure that our invoice tracker currently calculates the paid amount for each invoice. So first, we're going to do a simple SUMIF function.

Scenario 2: Customer makes payments that go across multiple invoices
If the customers, for example, has multiple invoices, like, for example, customer three has three invoices, and they actually pay together across multiple invoices in their payments. So how do you make sure that you can track the payments that the customers made and also allocate correctly to each invoice and calculate the outstanding amount. So that's what we're going to learn in this video.

Logic: Find the minimum of available payment amount or inquired invoice amount, whichever is the lower is going to be the result of the formula.
The available payment amount would be first the total, how much the customer has paid so far.

We will use SUMIF with OFFSET function.

One thing to keep in mind here is that we are using the offset from the top to the bottom, which means that it'll go in this order. So the order in which it's allocated is based on the order on which you enter the table.

If you have any questions or suggestions about this topic, please post them in the comments and I look forward to hearing your feedback.

-~-~~-~~~-~~-~-
Please watch my latest video: "Highlight events, weekends and holidays on calendar in Excel" https://www.youtube.com/watch?v=b0lWFlhAj3k
-~-~~-~~~-~~-~-

Видео Tracking Multiple Payments & Multiple Invoices in Excel канала Indzara