Forum Discussion
Managing Incoming Payments on Excel
I was wondering if there is a way (or a formula) to manage incoming payments on excel from different people. An example would be person 1 owes 100 dollars and person 2 owes 200 dollars but they each need to make separate payments towards separate "account" amounts. Is there a formula that will populate names and account balances/amounts and then type in the person's name so that the amount they pay towards it (say 10 dollars) will be subtracted from their individual amount.
| Michael | 5/30/2019 | $0.00 | $160.00 |
| Albert | 5/30/2019 | $0.00 | $160.00 |
| Rebecca | 5/30/2019 | $0.00 | $160.00 |
| Jacob | 5/30/2019 | $0.00 | $160.00 |
| Joseph | 5/30/2019 | $0.00 | $160.00 |
| Michael | 5/31/2019 | $10.00 | $150.00 |
In this table, "Michael" has 150 dollars at the bottom because he paid 10. Is there a formula or a way to differentiate Michael from the others? I have a simple formula that subtracts from the running balance, but it's cumulative (everyone basically pays towards the 160, but i need each of them to pay their own 160).
MichaelAyala , I'd suggest to separate credits and payments on two columns, as
Balance here is calculated as
=SUMIFS([Credit],[Name],[@Name],[Date],"<="&[@Date])- SUMIFS([Payment],[Name],[@Name],[Date],"<="&[@Date])
3 Replies
MichaelAyala , I'd suggest to separate credits and payments on two columns, as
Balance here is calculated as
=SUMIFS([Credit],[Name],[@Name],[Date],"<="&[@Date])- SUMIFS([Payment],[Name],[@Name],[Date],"<="&[@Date])
SergeiBaklan Thank you so much! Sometimes what seems like a complicated problem has the simplest solution.
MichaelAyala , you are welcome
