SOLVED

Managing Incoming Payments on Excel

Copper Contributor

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.

Michael5/30/2019$0.00$160.00
Albert5/30/2019$0.00$160.00
Rebecca5/30/2019$0.00$160.00
Jacob5/30/2019$0.00$160.00
Joseph5/30/2019$0.00$160.00
Michael5/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).

3 Replies
best response confirmed by MichaelAyala (Copper Contributor)
Solution

@MichaelAyala , I'd suggest to separate credits and payments on two columns, as

image.png

Balance here is calculated as

=SUMIFS([Credit],[Name],[@Name],[Date],"<="&[@Date])-
 SUMIFS([Payment],[Name],[@Name],[Date],"<="&[@Date])

 

@Sergei Baklan Thank you so much! Sometimes what seems like a complicated problem has the simplest solution.

@MichaelAyala , you are welcome

1 best response

Accepted Solutions
best response confirmed by MichaelAyala (Copper Contributor)
Solution

@MichaelAyala , I'd suggest to separate credits and payments on two columns, as

image.png

Balance here is calculated as

=SUMIFS([Credit],[Name],[@Name],[Date],"<="&[@Date])-
 SUMIFS([Payment],[Name],[@Name],[Date],"<="&[@Date])

 

View solution in original post