Dan Brewerton
asked on
Calculating balance due on invoices with multiple payments
Greetings experts. I am trying to determine the AR based on invoices that are not completely paid. I have been toiling around on the web trying to figure this out; I thought it would be much more simple than it is. So, I was wondering how to get this excel formula I found on ExcelJet to work properly. The current formula reads:
=IF(COUNTIF($A$2:A3,A3)=1,
My Columns are like so:
Invoice# Sales Order Repcode Account# Invoice Date Invoice Amount Date Paid Payment Customer PO AR Terms Due Date Amount Due
In the case of an invoice that a customer did more than one payment, I would like the total at the end to display nothing. Instead, the first line shows their balance due, then the second or further line shows blank if their balance on the invoice is 0.00. How do I get this formula to look at Invoice (column A) Invoice Amount (column F) and Payment (column H) so that if all balances are clear, do not show anything in the Amount Due (column L)? Any help would be super appreciated and thank you!
=IF(COUNTIF($A$2:A3,A3)=1,
My Columns are like so:
Invoice# Sales Order Repcode Account# Invoice Date Invoice Amount Date Paid Payment Customer PO AR Terms Due Date Amount Due
In the case of an invoice that a customer did more than one payment, I would like the total at the end to display nothing. Instead, the first line shows their balance due, then the second or further line shows blank if their balance on the invoice is 0.00. How do I get this formula to look at Invoice (column A) Invoice Amount (column F) and Payment (column H) so that if all balances are clear, do not show anything in the Amount Due (column L)? Any help would be super appreciated and thank you!
Can you supply a sample workbook?
ASKER
It is all invoices, but I'm only trying to do a slice of the data as the first 10 records.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No Comments added further
How is the sheet being generated, from a report in your invoicing system?