How to apply several payments on one invoice and get a balance from previous payment

[DianeBBB]

In a sales journal spreadsheet with multiple columns, date, invoice number, amt of inv, etc., I want to incorporate the cash receipts by adding columns for date rec'd, check number, amt rec'd, etc. It works fine when there is one payment, which I apply to the original invoice row. The question is that some payments are done in up to 8 installments. How do I apply these payments to original invoice row without adding too many columns? If added on a new row, how do these installments add up and get deducted from the balance amount?

 

[Jeffrey Mahoney]

I would think that you could use SUMIFS get what you need. There are too many unknowns for me.

When you enter the payment lines, are you specifying the invoice number on each payment line? If so, then you can calculate the balance easily. One part of the formula is to Sumifs the amount of the original invoice. The other part requires some anchoring to get it right. Let's assume the Invoice # is in column E, Invoice Amount is column H, and Amt Paid is column I. In cell I2, the formula would be: =sumifs($H$1:$H2,$E$1:$E2,E2)-sumifs($I$1:$I$2,$E1:$E2,E2)

Yes, I anchored the headers. Yes, I know you could use SUMIF instead; I tend to use SUMIFS all the time so I don't have to remember the difference between them. This will give you a running balance with each new payment line giving the remaining amount.

 

[DianeBBB]

Thanks Jeffrey. Trying to work it out with your suggested formula.