This is the template:
It calculates dues or outstanding amounts.
It shows paid invoice and status of the due. PD is the named range for the present date. It also informs of possible returns to client. the IF Function was used.
=IFERROR(IF([@Status]="Past Due",IF(PD-[@[Due Date]]It returns the duration of the due.
.
The formula returns information on invoice data. It uses the AGGREGATE Function.
It returns the number of invoices in a period of time.
It stores invoices within a given period.
=IFERROR("INVOICES: "&COUNTIFS(Invoice_Info[Status],"Recent",Invoice_Info[Selected],1)+COUNTIFS(Invoice_Info[Status],"Due Today",Invoice_Info[Selected],1),"")
=IFERROR(SUMIFS(Invoice_Info[Due],Invoice_Info[Status],"Recent", Invoice_Info[Selected],1)+SUMIFS(Invoice_Info[Due], Invoice_Info[Status],"Due Today",Invoice_Info[Selected],1),"")
It returns total invoices in recent times in B6.
It calculates the number of past dues in D5.
It stores past dues in D5.
New entries will update the invoice history.
Steps:
It stores the total invoice using the SUM function.
=SUM(G4:G8)
It stores the total paid amount.
It stores the total outstanding.
=F4-G4
It calculates row-wise outstanding.
The following chart showcases a template with random data to keep track of invoices and payments in Excel:
Steps:
It stores the address of the billing company. The VLOOKUP function looks for the CustomerList and the CONCATENATE Function for address and ZIP Code.
=IF(VLOOKUP(C3,CustomerList,4,FALSE)="","",IF(VLOOKUP(C3,CustomerList,5,FALSE)<>"",CONCATENATE(VLOOKUP(C3,CustomerList,5,FALSE),", ",VLOOKUP(C3,CustomerList,6,FALSE)),CONCATENATE(VLOOKUP(C3,CustomerList,6,FALSE))))
It returns the name of the city and state.
It returns the contact person’s name.
It returns the phone number.
It returns the customer’s email ID.
It returns the total invoice for your product.
It stores the total amount of the invoice.
It returns the amount to be paid.
The following chart showcases a template with random data:
Suppose EA Sports wants to order the following items. Insert the products and invoice information and select EA Sports from the drop down list to find their contact information.
Use the following template to practice:
Download Invoice & Payment Template (Free)
Invoices and Payments Tracker.xlsxMd. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency. Read Full Bio
2 CommentsHello, I hope all is well! I am trying to mirror your steps, and I’m having an issue when I get to the G6 Step. I keep writing the following formula =IFERROR(COUNTIFS(Invoice_Info[Due Period],E6,Invoice_Info[Selected],1),””)but every time i press enter it gives me an error. However, i’m not sure what the Invoice_Info is. This is what i end up doing – =IFERROR(SUMIFS(Table1[Due],Table1[Due Period],E6,Table1[Selected],1),””) but i’m not if that correct either.
Reply
Hi! Thanks for asking. As the article was to provide some templates, I didn’t go in detail with the formula. Here, Invoice_Info is a named range. It refers to the range B12:J17 of the ‘template 1’ sheet. To create a named range, you need to select your desired range of cells, then go to Data Tab >> Define Name and after that, give a name of that range. The advantage is that, you can use that range by inserting it’s name anywhere in the worksheet or workbook. You don’t have to select the range every time you use it in a formula. Hope this solves your problem.
