Tracking Multiple Payments & Multiple Invoices in Excel


welcome to this week’s episode of ask
your XO question in this week’s episode we will be learning how to write
formulas that will help us allocate multiple payments made against the same
invoice or multiple payments made by customers across multiple invoices so we
will learn how to write the formula in this video if you have not subscribed to
this channel yet please do and now let’s get started with this week’s episode now
just to set the stage for this video this we are going to use the invoice
tracker excel template that is available for free to download from n 0 calm I
will also put a link to the video and also the page where you can download
this template in the video description so please check that out and in this
template that we have published a while ago the invoices are recorded here 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 been entered directly in this column and then
the outstanding amount and what’s the status and everything will be calculated
by the template using formulas automatically now in this template we
have entered the paid amount directly as a user input right so what we’re going
to do now is to extend this template further by saying what if this invoice
number 1 the customer did not make one payment made in multiple payments so
what will you do if the customer for example has multiple invoices like for
example customer 3 has 3 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 ok so now I have removed a lot of the sample data so I’ve kept it really
simple so that we can clearly follow what’s going on with the
formula and the first option here is the payment by invoice so I’m gonna go into
the sheet but I’ve already created a table so 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 so this means for invoice number one the
customer made two payments one on December 5th $400 one on December 8th
for $200 and the similarly for invoice number two there were two payments made
two hundred and four hundred dollars on different dates now we want to make sure
that our invoice tracker collect correctly calculates the paid amount for
each invoice so first we’re going to do a simple sum if function so we’re going
to you know use the table that we’ve already created so before I do that I
just want to inform that this is an Excel table where you can enter more and
more rows and it’ll automatically expand so the formula will work only if you
have created like a table and we have named the table as t underscore pym t
underscore I and V so this is kind of important because this will come through
in the formula so we’re gonna write in the first cell in the table for a paid
amount column sum if open paranthesis now we need to provide the range the
range is going to be the invoice number column in this table and comma and what
we are going to search for is the specific invoice number here so I click
here and you will see that in the formula it says at invoice number and
then comma now what are we going to sum we are going to sum the payment amount
here close parenthesis and enter so now we can see that the formula is correctly
calculating 300 dollars for the first invoice so if we go back here first
invoice 100 plus 200 300 invoice number two two hundred plus four hundred six
hundred dollars so I’m going to go back here and the second invoice say $600
third and the fourth invoices no payments have been made so they are zero
so this is how you can calculate the paid amount if you have customer making
multiple payments for a single invoice the second scenario we are going to deal
with is a little bit more complicated where the customer is making payment
across invoices so that’s a little bit complicated but we will deal with that
as well with the formula so I’m going to first delete the formula in this now and
then we will start entering a new formula for that scenario before I do
that the payments made by customer are tracked in this table so you will create
a similar table and say customer when they made the payment payment date and
the amount of they paid on that day so now I have customer one making a payment
of $100 on December 5th ousand dollars on December 8th
similarly customer two makes two payments on two different dates these
payments are not specific to any invoices we are going to allocate
according to the sequence the payment made and then we will just keep
allocating the amounts for each invoice so we go back here this is where we are
going to write the formula so the first customer the customer one has two
invoices invoice one and invoice two which is five hundred and thousand so
totally thousand five hundred but the customer made a payment of hundred and
then thousand so this is where it gets tricky because the payment made by the
cus the first payment made by the customer is less than this invoice
amount but the second payment is greater than this invoice amount so now in order
to write the formula remember the logic so the logic is going to be find the
minimum of available payment amount or the required invoice amount whichever is
the lower is going to be the result of the formula so now let’s start writing
the formula so that it becomes clearer so what we’re going to do is to first
find out what is the available payment amount so
payment amount will be first the total how much the customer has paid so far so
that means some if and then we are going to go into the payment thing and then
say we don’t need this part we’ll just go and do customer comma this is very
similar to the previous formula we wrote so now instead of invoice number I have
to do customer because we’re doing everything by customer now and then the
sum range will be the payment amount by the customer and then do the circuit so
now this is the total amount that the customer is paid so customer one has
paid $1,100 and then the problem with this is that now the 1100 will get
allocated every time the customer one appears it’ll allocate 1100 every time
the customer 2 appears in allocate 700 because this formula will give the total
amount paid but we want to subtract amount that is already been allocated to
previous invoices so this is the part which is a little bit complicated so I’m
gonna hit alt enter and then I’m going to do – and then we will do a sum if
again so in this sum F what we have what we are going to do is to use the offset
function so we’ll do offset offset requires a starting point so I will
start from here the customer header and then comma how many rows should I go
zero how many columns should I go zero comma and then what’s the height of my
range and this is where I’m going to use a growth function to say this is our
current row right that’s where we are writing the formula so current row minus
row of the header which is this and now this when I close the parentheses so
this gives me the range of all the customers until now so it will not
include the current cell current row it will include all the previous rows so
for all the previous rows of customers I’m gonna copy this part because it will
be helpful for me and I’m gonna do comma and the sum requires a criteria so what
are we looking for we want to know that exactly the rows
only where this customer is there so I’m going to click in here and that becomes
at customer and now I’ll do a comma again and now what are we going to sum
up we’re going to sum up all the paid amount column in previous rows in all
the previous rows we want that so this is where I’m going to just paste what I
copied earlier which is our offset function the only thing which is going
to be different here is we’re not going to start from the customer header we’re
going to start from the paid amount column header so it’ll start from here
and it will go all the way and before and it will stop before the current row
and I will use an actual example to illustrate that better but I’m gonna
close this sum if now and let’s check if the sum of closest correct okay so now
we have closed the sum if so this tells us the all the allocated amount the
total thing will give us the allocated amount this one and then this one is the
total paid by the customer so total paid minus total allocated will give us how
much can we be allocating to this invoice so I’m gonna hit enter so we’ll
see what happens here so all the 11-hundred has been allocated
to the first invoice and which means for the second invoice there’s nothing to be
allocated because we have already allocated all of the paid amounts
similarly for customer to all the $700 have been allocated to this invoice so
nothing is left to be allocated here so we are going in the right direction that
we don’t have the 1100 in both places now it’s only one place however you
don’t want to allocate $1,100 to an invoice which is only 500 right so
that’s the last part of the formula which is to do a minimum function say
minimum of the whole thing like this amount and put a comma and then say if
the invoice amount is less then just allocate only the invoice amount so
close parentheses hit OK now what happens is the 500 has been allocated to
the invoice number 1 and then the remaining 600 are allocated to the
invoice number 2 so 500 now is paid in full
no outstanding amount but the second invoice only 600 has been allocated the
remaining 400 amount should be paid by the customer this is because totally
1100 is what the customer one has paid totally 700 is what the customer 2 has
paid and so 700 is allocated here remaining thousand 300 is still do so it
is calculating correctly so so just to recap again what’s happening here
we have the sum if this looks at the total amount that the customers paid so
far which includes all the payments and that payments by customer table and then
this part is actually calculating what has already been allocated towards
paying previous invoices so all the previous invoices payments made should
be subtracted and so the difference here now the difference here will tell us how
much more can be allocated and then we compare that with the invoice amount and
then we take the minimum value so this is the formula that will help us
correctly allocate the payments to watch multiple invoices one thing to keep in
mind here is that we are using the offset from the top to the bottom so
which means that it’ll go in this order so if the order in which it’s allocated
is based on the order in which you have entered 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 I’ll see you all in the next
week’s episode of ask your Excel question thank you very much for
watching you

21 Comments

  • sir my question is how to match the online platform payment reconcile like Flipkart amazon Paytm etc. please help me

  • For the free Invoice Tracker template used in this video, please visit https://indzara.com/2016/07/invoice-tracker-template-free/
    Best wishes.

  • can i use Vlookup Formula to Bring Multiple Payments In Paid Amount Column with Sum

  • Sir, may I know why I follow your scenario of payment across multiple invoices at 7:25, I got the answer of paid amount 1100, 1000, 700, 400 instead of 1100, 1100, 700, 700? Can I know what is the reason?

  • Thank you sir , very well payment system, But The Link is not working,PLEASE Send me working excl site,my mail address [email protected]

  • Sir i have tried this formula many times but it is not working in my workbook kindly help me in correcting this problem. Thanks

  • =SUMIF(Table1[Customer Name],[@[Customer Name]],Table1[[Payments ]])-SUMIF(OFFSET(Table2[[#Headers],[Customer Name]],0,0,ROW([@[Customer Name]])-ROW(Table2[[#Headers],[Customer Name]]),[@[Customer Name]])-OFFSET(Table2[[#Headers],[Customer Name]],0,0,ROW([@[Customer Name]])-ROW(Table1[[#Headers],[Customer Name]])

  • Great! Works like a charm. How can I get the payment date also corresponding to the payment amount?

  • How do I start? I already downloaded it but the template isn't showing.

  • Can I have a excel demo copy?Tracking Multiple Payments & Multiple Invoices in Excel,Pls Pls………….invoice tracker Sample demo excel copy.Pls send my email [email protected]

  • How r u sir..you r great. Please help me..for suggest.. how to change due date period ..

  • can i ask for a copy of the file with the payments by customer tab already? the equation i followed every step but my excel file keeps saying error in formula or i have a missing parentheses even if I check 🙁

  • can show as with sumifs formula as i have multiple payment and multiple sales in one sheet

  • this is awesome
    i really did enjoy the lessen and the way u explained but when i'm trying to implement that excel gives me an error so i'm wondering can u help me out if i send u the file?

  • hello, I am working on a excel sheet regarding Invoice payments. All invoices are listed in excel as a summary sheet. I have already created due days from invoice date to to-date. In the receivable column payment if the payment received i need the due days should show Paid if the receivable column is 0 (not paid) value then I still need to see the due days as usual. is this function possible in excel? Please help.

  • This is superb, and exactly what i wanted, Thanks a ton for the uploand. One challange here with me, my date is not sorted by date, pls suggest is there any way out for it.

  • Indzara: Suppose If after knocking multiple invoices, some part of one of the last payment cannot be knocked off again any Invoice which should be treated as advance. Is it possible the advance could be shown against the last invoice in minus??

  • Respected Sir, can u pls help me…I have a doubt.. the main thing is that if a payment is coming after the credit period then for the date difference an interest of 20% or whatever the rate,should be calculated.. How can we do that in this file itself

  • Sir, the customer wise formula isn't taking it's says the formula you typed contains an error
    Please help

  • I have entered too many arguments for this function. But I don't know what I am missing?
    =SUMIF('PAYMENT BY CUSTOMER'!A2:A50,[@CUSTOMER],'PAYMENT BY CUSTOMER'!C2:C50)- SUMIF(OFFSET(T_INV[[#Headers],[CUSTOMER]],0,0,ROW([@CUSTOMER]-(ROW(T_INV[[#Headers],[CUSTOMER]])),[@CUSTOMER],OFFSET(T_INV[[#Headers],[PAID AMOUNT]],0,0,ROW([@CUSTOMER]-(ROW(T_INV[[#Headers],[CUSTOMER]])))

  • Hello Sir,
    Kya hum FIFO basis se Debtors ki outstanding nikal sakate hain
    Hamare paas opening balances, Receipt, Sales ka data hain opening + sales – Receipt Adjustments FIFO basis = Net outstanding Bill wise & uska aging 0-30, 30-60, 60-90, 90 above nikal na hain

    [email protected]
    9420023221

Leave a Comment

Your email address will not be published. Required fields are marked *