May 31 2019 10:42 PM
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).
Jun 01 2019 04:40 AM
Solution@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])
Jun 11 2019 09:38 PM
@Sergei Baklan Thank you so much! Sometimes what seems like a complicated problem has the simplest solution.
Jun 01 2019 04:40 AM
Solution@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])