# 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

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