I hv 4 different columns of Database
╔════╦════════════╦═════════╦═════════════╦═════════════╗
║ ║ A ║ B ║ C ║ D ║
╠════╬════════════╬═════════╬═════════════╬═════════════╣
║ 1 ║ Date ║ Party ║ Debit ║ Credit ║
║ 2 ║ 25-12-2019 ║ John ║ 50,000 ║ ║
║ 3 ║ 27-12-2019 ║ Neil ║ 50,000 ║ ║
║ 4 ║ 29-12-2019 ║ John ║ 1,00,000 ║ ║
║ 5 ║ 01-Jan ║ Neil ║ 50,000 ║ ║
║ 6 ║ 05-Jan ║ John ║ 8,00,000 ║ ║
║ 7 ║ 08-Jan ║ John ║ 70,000 ║ ║
║ 8 ║ 28-Jan ║ Neil ║ 20,000 ║ ║
║ 9 ║ 02-Feb ║ John ║ 30,000 ║ ║
║ 10 ║ 15-Feb ║ Neil ║ 19,000 ║ ║
║ 11 ║ 27-Feb ║ John ║ 21,000 ║ ║
║ 12 ║ 04-Mar ║ John ║ 22,000 ║ ║
║ 13 ║ 08-Mar ║ Neil ║ 88,000 ║ ║
║ 14 ║ 09-Mar ║ John ║ 6,00,000 ║ ║
║ 15 ║ 05-Apr ║ John ║ 7,05,000 ║ ║
║ 16 ║ 21-Apr ║ Rebbika ║ 25,00,000 ║ ║
║ 17 ║ 05-May ║ John ║ ║ 7,00,000 ║
║ 18 ║ 07-May ║ Neil ║ ║ 5,00,000 ║
║ 19 ║ 19-May ║ John ║ ║ 1,00,000 ║
║ 20 ║ 21-May ║ Rebbika ║ ║ 10,00,000 ║
╚════╩════════════╩═════════╩═════════════╩═════════════╝
and here is the result table (Which actually is a Trial of each Party with monthly remaining balance
╔═══╦═════════╦════════╦════════╦═══════╦════════╦═════════╗
║ ║ E ║ F ║ G ║ H ║ I ║ J ║
╠═══╬═════════╬════════╬════════╬═══════╬════════╬═════════╣
║ 1 ║ Party ║ Dec-19 ║ Jan-20 ║ Feb-20║ Mar-20 ║ Apr-20 ║
║ 2 ║ John ║ - ║ 220000 ║ 51000 ║ 622000 ║ 705000 ║
║ 3 ║ Neil ║ 10000 ║ 70000 ║ 19000 ║ 88000 ║ 0 ║
║ 4 ║ Rebbika ║ - ║ - ║ - ║ - ║ 1500000 ║
╚═══╩═════════╩════════╩════════╩═══════╩════════╩═════════╝
and this is the Formula i am using in E2 to J4. The formula i copied is from Cell I2
=IF(SUMPRODUCT(--(MONTH($A$2:$A$20)=12)+--(MONTH($A$2:$A$20)<=2),--($B$2:$B$20=F3),$C$2:$C$20)-SUMIF($B$2:$B$20,F3,$D$2:$D$20)>1,SUMPRODUCT(--(MONTH($A$2:$A$20)=3),--($B$2:$B$20=F3),$C$2:$C$20),IF(SUMPRODUCT(--(MONTH($A$2:$A$20)=3),--($B$2:$B$20=F3),$C$2:$C$20)-(SUMIF($B$2:$B$20,F3,$D$2:$D$20)-SUMPRODUCT(--(MONTH($A$2:$A$20)=12)+--(MONTH($A$2:$A$20)<=2),--($B$2:$B$20=F3),$C$2:$C$20))<1,"",SUMPRODUCT(--(MONTH($A$2:$A$20)=3),--($B$2:$B$20=F3),$C$2:$C$20)-(SUMIF($B$2:$B$20,F3,$D$2:$D$20)-SUMPRODUCT(--(MONTH($A$2:$A$20)=12)+--(MONTH($A$2:$A$20)<=2),--($B$2:$B$20=F3),$C$2:$C$20))))
E.g:- Total Due Payment of Dec to John is 150,000
Payment received has nothing to do with month or date, its just the whole payment we have received till the date So, If he has paid us 120,000
Balance of Dec=30,000, and Jan-Feb-Marc... Due payment is still pending as it is
If we have Received the Total Payment 1,021,000
Then it will clear the 150,000 of Dec and 870,000 of Jan, and 1,000 from the Feb and will Show Dec Column as Nill, Jan=Nill, Feb 50,000
Total of Received Amount should be subtracted from the oldest month of billing
But the function i am using is really messy and making sheet a bit heavy, Is there any possible shorter and nice way to perform this task without using vba codes but with shorter formula than the one i am trying. its MS 2007
Any help will be appreciated
I finally Got it
=IFERROR(1/(1/ROUND(MAX(0,SUMIFS($C:$C,$B:$B,$E2,$A:$A,"<="&EOMONTH(F$1,0))-SUMIF($B:$B,$E2,$D:$D)-SUM($E2:E2)),0)),"-")
Dragging it vertically and horizontally worked perfectly fine.