Managing Payment table separate from billing, calculating lps on every received payment based on criteria.

N

NaushadAnwar

New Member
Joined
Oct 8, 2017
Messages
11
Current Scenarios:

My office colleagues are recording account receivable and sales data in sheet4, however, some companies have started multiple payments during the month and it is now difficult to manage them in this format.

Action: so I decided to make new sheet for recording payments, which is working fine if company have no multiple payments during the month, because by these payment received we have to calculate LPS late payment surcharge by criteria which is available in sheet2 column r2:t6, by implementing this I have also availed Current LPS which has to be charge in next month.

Problem: now the problem is if company have made 2 payments 1 is on time and 1 is late, LPS to be charge on each payment using different criteria. And for that criteria I need sub-total and total column from sheet4, which is repeating in sheet2 and I cant’ get current lps.

Future decision:

When I can compute LPS , I will formulate sheet4, and current LPS for next period, issue date, due date and received payment columns will be linked from sheet2 to sheet4 on the basis of Billing Period, so that in sheet2 have only billing information and linked payment details.





sheet4

billing schedule.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
3S.NPeriodissue dateDue DateULB # Previous Reading Curent Reading Diff Line losses unit Total Rate Unit AmountMeter RentPre Adj Amount Previous amount Total Current LPS Previous LPS Total LPS Sub Total Received Amount BalanceReceipt No.
4Opening -----
527Apr-2103.05.202109.05.2021755611,94714,5662,619932,71229.780,54620080,566203,025283,5923,8703,870287,462100,000187,462RR.# 41974/24.05.2021
628May-2101.06.202111.06.2021762114,56616,7892,2234412,66429.8379,46720079,487187,462266,9494,3124,312271,261271,261
729Jun-2105.07.202112.07.2021765916,78919,2942,505562,56130.9779,31420079,334271,261350,5954,0694,069354,664354,664
830Jul-2104.08.202113.08.2021775119,29421,7092,4152132,62829.6677,94620077,966354,664432,6315,3205,320437,951341,00096,951RR.# 42580/13.08.2021 RR.# 42617/23.08.2021 RR.# 42634/24.08.2021 RR.# 42638/26.08.2021
931Aug-2103.09.202113.09.2021781821,70924,0472,3384042,74230.7384,26220084,28296,951181,2331,4541,454182,687182,687
1032Sep-2130.09.202112.10.2021791324,04727,0282,981953,07631.998,12420098,144182,687280,8312,7402,740283,571283,571
1133Oct-2128.10.202108.11.2021802127,02829,8852,8575123,36930.82103,833200103,853283,571387,4244,2544,254391,67850,000341,678RR.# 44425/29.11.2021
1234Nov-2107.12.202115.12.2021805829,88532,5692,6841262,81032.3490,87520090,895341,678432,5735,1255,125437,69890,000347,698RR.# 44442/74/08/15/.12.21
1335Dec-2129.12.202110.01.2022811832,56934,4711,9024142,31641.8896,99420097,014347,698444,7125,2155,215449,928449,928
1436Jan-2234,47136,3931,9224572,37930.7773,20220073,222449,928523,1506,7496,749529,89820,000509,898RR.# 43544/09.02.2022
1537Feb-2204.03.202214.03.2022829136,39338,7502,3571432,50030.776,75020076,770509,898586,6687,6487,648594,31780,000514,317RR.# 43622/07.03.2022
1638Mar-2231.03.202212.04.2022835738,75041,0032,2533682,62129.3176,82220076,842514,317591,1587,7157,715598,873598,873
1739Apr-2230.04.202212.05.2022843141,00343,4492,4463472,79334.6696,80520096,825598,873695,6998,9838,983704,682704,682
1840May-2202.06.202213.06.2022846943,44945,6582,2093362,54533.2484,59620084,616704,682789,29810,57010,570799,868170,000629,868RR.# 43922+24/05.07.2022
1941Jun-2205.07.202214.07.2022853545,65847,8422,1842662,45036.7289,96420089,984629,868719,8529,4489,448729,30090,000639,30043958/27/07/2021
2042Jul-2205.08.202215.08.2022859147,84249,7661,9244702,39444.94107,586200107,606639,300746,90610,93910,939757,846120,000637,846RR.# 44022/17.08.2022
2143Aug-22871149,76651,9052,1391,2833,42258.38199,776200199,796637,846837,64211,36811,368849,01025,000824,010RR.# 44099/16.09.2022
2244Sep-2211.10.202217.10.2022878651,90554,1492,2446292,87353.26153,01620-17177135,859824,009959,86812,36012,360972,229100,000872,229RR.# 44174/10.10.2022
23Sep-2211.10.202217.10.2022872,229872,229872,22920,000852,229R.R # 44249/28.10.2022
2445Oct-22885854,14956,4092,2605362,79636.8102,89320102,913852,229955,14212,78312,783967,92540,000927,925RR.# 44268/07.11.2022
25Oct-22927,925927,925927,925100,000827,925R.R. # 44283/10/11/2022
26Oct-22827,925827,925827,925100,000727,925R.R. # 44299/14/11/2022
27Oct-22727,925727,925727,92550,000677,925R.R. # 44294/11/11/2022
2846Nov-2209.12.202216.12.2022893056,40958,4182,0098562,86534.7899,6452099,665927,925########10,16910,169787,759125,000662,759RR.# 44379/05.12.2022 RR.# 44389/08.12.2022 RR.# 44532/16.12.2022
2947Dec-2211.01.202317.01.2023900758,41859,8431,4256172,04238.0777,7392077,759662,759740,5189,9419,941750,459750,459
3048Jan-2314.02.202320.02.2023910959,84360,9341,0915491,64046.7476,6542076,674750,459827,13311,25711,257838,389120,000718,389RR.# 44697/13.02.2023 RR.# 44747/28.02.2023
3149Feb-2313.03.202320.03.2023918060,93462,1361,202-1,20228.5334,2932034,313718,389752,70210,77610,776763,47860,000703,478R.R # 44789/13.03.2023
3250Mar-2307.04.202317.04.2023925362,13663,9501,8141761,99067.26133,84720133,867703,478837,34610,55210,552847,898100,000747,898R.R # 44914/030.04.2023
3351Apr-2312.05.202319.05.2023930763,95065,8841,9342442,17859.95130,57120130,591747,898878,48911,21811,218889,707120,000769,707R.R # 44975/18.05.2023
3452May-2330.05.202308.06.2023937365,88467,9722,088742,16253.35115,34320115,363769,707885,07011,54611,546896,616150,000746,616R.R # 45063/20.06.2023 R.R # 45062/20.06.2023
3553Jun-2320.06.202327.06.2023943567,97270,0422,070742,14453.35114,38220114,402746,616861,01811,19911,199872,217872,217
36625,236100(17,177)800,272################78,818-78,818################-
Sheet4
Cell Formulas
RangeFormula
V8V8=70000+31000+80000+160000
V12V12=35000+55000
V18V18=170000
V22V22=100000
P23:P27,P29:P35P23=W22
Q23:Q35Q23=O23+P23
P28P28=W24
V24V24=40000
U23:U27U23=T23+Q23
V28V28=55000+40000+30000
V30V30=80000+40000
V33V33=120000
V34V34=35000+115000
W22:W35W22=U22-V22


sheet2:
Company NameBilling PeriodULB #Sub TotalReceived AmtPayment DateStatus Amount Total Current LPSturnover in daysColumn1
(Lacandela) Relible A-3830-Sep-221800195,89817-Oct-22On Time Full], $C$3:C3,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B3&"'!$A:$A"), $C3, INDEX(INDIRECT("'"&$B3&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B3&"'!$A$2:$Z$2"), 0))),""))]194,975,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C3,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C3,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]0.00
(Lacandela) Relible A-3831-Oct-22170014,59318-Nov-22Late Payment Full], $C$3:C4,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B4&"'!$A:$A"), $C4, INDEX(INDIRECT("'"&$B4&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B4&"'!$A$2:$Z$2"), 0))),""))]14,593,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]219="Not Paid", XLOOKUP(C4,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C4,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-4.00
(Lacandela) Relible A-3830-Nov-22150029,93122-Dec-22Late Payment Full], $C$3:C5,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B5&"'!$A:$A"), $C5, INDEX(INDIRECT("'"&$B5&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B5&"'!$A$2:$Z$2"), 0))),""))]37,880,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]568="Not Paid", XLOOKUP(C5,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C5,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-6.00
(Lacandela) Relible A-3831-Dec-22016,23031-Jan-23Late Payment Full], $C$3:C6,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B6&"'!$A:$A"), $C6, INDEX(INDIRECT("'"&$B6&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B6&"'!$A$2:$Z$2"), 0))),""))]15,780,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]237="Not Paid", XLOOKUP(C6,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C6,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-14.00
(Lacandela) Relible A-3831-Jan-23013,95828-Feb-23Late Payment Full], $C$3:C7,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B7&"'!$A:$A"), $C7, INDEX(INDIRECT("'"&$B7&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B7&"'!$A$2:$Z$2"), 0))),""))]13,714,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]206="Not Paid", XLOOKUP(C7,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C7,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-8.00
(Lacandela) Relible A-3831-Mar-23025,42420-Apr-23Late Payment Full], $C$3:C8,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B8&"'!$A:$A"), $C8, INDEX(INDIRECT("'"&$B8&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B8&"'!$A$2:$Z$2"), 0))),""))]25,120,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]377="Not Paid", XLOOKUP(C8,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C8,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-3.00
(Lacandela) Relible A-3831-May-23076,22107-Jun-23On Time Full], $C$3:C9,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B9&"'!$A:$A"), $C9, INDEX(INDIRECT("'"&$B9&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B9&"'!$A$2:$Z$2"), 0))),""))]75,295,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C9,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C9,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]1.001-30 days overdue
(Lacandela) Relible A-3830-Jun-230Not Paid], $C$3:C10,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B10&"'!$A:$A"), $C10, INDEX(INDIRECT("'"&$B10&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B10&"'!$A$2:$Z$2"), 0))),""))]40,246,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]604="Not Paid", XLOOKUP(C10,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C10,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-16.00
Bahria Boat Building Yard C-3630-Sep-221711100,00010-Oct-22On Time Full], $C$3:C11,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B11&"'!$A:$A"), $C11, INDEX(INDIRECT("'"&$B11&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B11&"'!$A$2:$Z$2"), 0))),""))]1,832,097,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C11,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C11,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3630-Sep-22171120,00028-Oct-22Late Payment Full], $C$3:C12,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B12&"'!$A:$A"), $C12, INDEX(INDIRECT("'"&$B12&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B12&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C12,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C12,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-11.00
Bahria Boat Building Yard C-3631-Oct-22040,00007-Nov-22On Time Full], $C$3:C13,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B13&"'!$A:$A"), $C13, INDEX(INDIRECT("'"&$B13&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B13&"'!$A$2:$Z$2"), 0))),""))]3,438,917,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C13,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C13,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3631-Oct-220100,00010-Nov-22On Time Full], $C$3:C14,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B14&"'!$A:$A"), $C14, INDEX(INDIRECT("'"&$B14&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B14&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C14,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C14,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]4.001-30 days overdue
Bahria Boat Building Yard C-3631-Oct-220100,00011-Nov-22On Time Full], $C$3:C15,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B15&"'!$A:$A"), $C15, INDEX(INDIRECT("'"&$B15&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B15&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C15,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C15,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]3.001-30 days overdue
Bahria Boat Building Yard C-3631-Oct-22050,00014-Nov-22On Time Full], $C$3:C16,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B16&"'!$A:$A"), $C16, INDEX(INDIRECT("'"&$B16&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B16&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C16,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C16,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]0.00
Bahria Boat Building Yard C-3630-Nov-2255,00005-Dec-22On Time Full], $C$3:C17,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B17&"'!$A:$A"), $C17, INDEX(INDIRECT("'"&$B17&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B17&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C17,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C17,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]11.001-30 days overdue
Bahria Boat Building Yard C-3630-Nov-2240,00008-Dec-22On Time Full], $C$3:C18,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B18&"'!$A:$A"), $C18, INDEX(INDIRECT("'"&$B18&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B18&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C18,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C18,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]8.001-30 days overdue
Bahria Boat Building Yard C-3630-Nov-2230,00016-Dec-22On Time Full], $C$3:C19,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B19&"'!$A:$A"), $C19, INDEX(INDIRECT("'"&$B19&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B19&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C19,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C19,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]0.00
Bahria Boat Building Yard C-3631-Jan-2380,00013-Feb-23On Time Full], $C$3:C20,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B20&"'!$A:$A"), $C20, INDEX(INDIRECT("'"&$B20&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B20&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C20,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C20,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3631-Jan-2340,00028-Feb-23Late Payment Full], $C$3:C21,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B21&"'!$A:$A"), $C21, INDEX(INDIRECT("'"&$B21&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B21&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C21,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C21,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-8.00
Bahria Boat Building Yard C-3628-Feb-2360,00013-Mar-23On Time Full], $C$3:C22,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B22&"'!$A:$A"), $C22, INDEX(INDIRECT("'"&$B22&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B22&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C22,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C22,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3631-Mar-230100,00030-Apr-23Late Payment Full], $C$3:C23,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B23&"'!$A:$A"), $C23, INDEX(INDIRECT("'"&$B23&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B23&"'!$A$2:$Z$2"), 0))),""))]837,346,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]12,560="Not Paid", XLOOKUP(C23,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C23,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-13.00
Bahria Boat Building Yard C-3630-Apr-230120,00008-May-23On Time Full], $C$3:C24,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B24&"'!$A:$A"), $C24, INDEX(INDIRECT("'"&$B24&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B24&"'!$A$2:$Z$2"), 0))),""))]878,489,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C24,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C24,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]11.001-30 days overdue
Bahria Boat Building Yard C-3631-May-23035,00020-Jun-23Late Payment Full], $C$3:C25,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B25&"'!$A:$A"), $C25, INDEX(INDIRECT("'"&$B25&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B25&"'!$A$2:$Z$2"), 0))),""))]885,070,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]13,276="Not Paid", XLOOKUP(C25,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C25,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-12.00
Bahria Boat Building Yard C-3631-May-230115,00020-Jun-23Late Payment Full], $C$3:C26,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B26&"'!$A:$A"), $C26, INDEX(INDIRECT("'"&$B26&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B26&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C26,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C26,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-12.00
#N/A
#N/A
#N/A
#N/A
#N/A
Total29


lps criteria
On Time Full1No LPS is charge full received sub total
Late Payment Full215% LPS is charge on his Total
On Time Partial315% LPS is charge on sub-total - received amount = Balance
Late Payment Partial415% LPS is charge on his Total
Not Paid515% LPS is charge on his Total
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
reposting sheet2 without error
Company NameBilling PeriodULB #Sub TotalReceived AmtPayment DateStatus Amount Total Current LPSturnover in daysColumn1
(Lacandela) Relible A-3830-Sep-221800], $C$3:C3,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B3&"'!$A:$A"), $C3, INDEX(INDIRECT("'"&$B3&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B3&"'!$A$2:$U$2"), 0))), ""))]195,898195,89817-Oct-22On Time Full], $C$3:C3,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B3&"'!$A:$A"), $C3, INDEX(INDIRECT("'"&$B3&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B3&"'!$A$2:$U$2"), 0))), ""))]133,440], $C$3:C3,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B3&"'!$A:$A"), $C3, INDEX(INDIRECT("'"&$B3&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B3&"'!$A$2:$Z$2"), 0))),""))]194,975,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C3,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C3,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]0.00
(Lacandela) Relible A-3831-Oct-221700], $C$3:C4,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B4&"'!$A:$A"), $C4, INDEX(INDIRECT("'"&$B4&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B4&"'!$A$2:$U$2"), 0))), ""))]14,59314,59318-Nov-22Late Payment Full], $C$3:C4,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B4&"'!$A:$A"), $C4, INDEX(INDIRECT("'"&$B4&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B4&"'!$A$2:$U$2"), 0))), ""))]14,593], $C$3:C4,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B4&"'!$A:$A"), $C4, INDEX(INDIRECT("'"&$B4&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B4&"'!$A$2:$Z$2"), 0))),""))]14,593,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]219="Not Paid", XLOOKUP(C4,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C4,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-4.00
(Lacandela) Relible A-3830-Nov-221500], $C$3:C5,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B5&"'!$A:$A"), $C5, INDEX(INDIRECT("'"&$B5&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B5&"'!$A$2:$U$2"), 0))), ""))]29,93029,93122-Dec-22Late Payment Full], $C$3:C5,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B5&"'!$A:$A"), $C5, INDEX(INDIRECT("'"&$B5&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B5&"'!$A$2:$U$2"), 0))), ""))]29,931], $C$3:C5,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B5&"'!$A:$A"), $C5, INDEX(INDIRECT("'"&$B5&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B5&"'!$A$2:$Z$2"), 0))),""))]37,880,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]568="Not Paid", XLOOKUP(C5,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C5,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-6.00
(Lacandela) Relible A-3831-Dec-220], $C$3:C6,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B6&"'!$A:$A"), $C6, INDEX(INDIRECT("'"&$B6&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B6&"'!$A$2:$U$2"), 0))), ""))]16,22916,23031-Jan-23Late Payment Full], $C$3:C6,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B6&"'!$A:$A"), $C6, INDEX(INDIRECT("'"&$B6&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B6&"'!$A$2:$U$2"), 0))), ""))]15,781], $C$3:C6,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B6&"'!$A:$A"), $C6, INDEX(INDIRECT("'"&$B6&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B6&"'!$A$2:$Z$2"), 0))),""))]15,780,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]237="Not Paid", XLOOKUP(C6,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C6,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-14.00
(Lacandela) Relible A-3831-Jan-230], $C$3:C7,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B7&"'!$A:$A"), $C7, INDEX(INDIRECT("'"&$B7&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B7&"'!$A$2:$U$2"), 0))), ""))]13,95813,95828-Feb-23Late Payment Full], $C$3:C7,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B7&"'!$A:$A"), $C7, INDEX(INDIRECT("'"&$B7&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B7&"'!$A$2:$U$2"), 0))), ""))]13,715], $C$3:C7,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B7&"'!$A:$A"), $C7, INDEX(INDIRECT("'"&$B7&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B7&"'!$A$2:$Z$2"), 0))),""))]13,714,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]206="Not Paid", XLOOKUP(C7,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C7,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-8.00
(Lacandela) Relible A-3831-Mar-230], $C$3:C8,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B8&"'!$A:$A"), $C8, INDEX(INDIRECT("'"&$B8&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B8&"'!$A$2:$U$2"), 0))), ""))]25,42425,42420-Apr-23Late Payment Full], $C$3:C8,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B8&"'!$A:$A"), $C8, INDEX(INDIRECT("'"&$B8&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B8&"'!$A$2:$U$2"), 0))), ""))]18,853], $C$3:C8,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B8&"'!$A:$A"), $C8, INDEX(INDIRECT("'"&$B8&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B8&"'!$A$2:$Z$2"), 0))),""))]25,120,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]377="Not Paid", XLOOKUP(C8,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C8,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-3.00
(Lacandela) Relible A-3831-May-230], $C$3:C9,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B9&"'!$A:$A"), $C9, INDEX(INDIRECT("'"&$B9&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B9&"'!$A$2:$U$2"), 0))), ""))]76,22176,22107-Jun-23On Time Full], $C$3:C9,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B9&"'!$A:$A"), $C9, INDEX(INDIRECT("'"&$B9&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B9&"'!$A$2:$U$2"), 0))), ""))]38,645], $C$3:C9,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B9&"'!$A:$A"), $C9, INDEX(INDIRECT("'"&$B9&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B9&"'!$A$2:$Z$2"), 0))),""))]75,295,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C9,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C9,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]1.001-30 days overdue
(Lacandela) Relible A-3830-Jun-230], $C$3:C10,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B10&"'!$A:$A"), $C10, INDEX(INDIRECT("'"&$B10&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B10&"'!$A$2:$U$2"), 0))), ""))]40,246Not Paid], $C$3:C10,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B10&"'!$A:$A"), $C10, INDEX(INDIRECT("'"&$B10&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B10&"'!$A$2:$U$2"), 0))), ""))]40,246], $C$3:C10,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B10&"'!$A:$A"), $C10, INDEX(INDIRECT("'"&$B10&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B10&"'!$A$2:$Z$2"), 0))),""))]40,246,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]604="Not Paid", XLOOKUP(C10,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C10,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-16.00
Bahria Boat Building Yard C-3630-Sep-221711], $C$3:C11,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B11&"'!$A:$A"), $C11, INDEX(INDIRECT("'"&$B11&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B11&"'!$A$2:$U$2"), 0))), ""))]1,844,458100,00010-Oct-22On Time Partial], $C$3:C11,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B11&"'!$A:$A"), $C11, INDEX(INDIRECT("'"&$B11&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B11&"'!$A$2:$U$2"), 0))), ""))]135,859], $C$3:C11,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B11&"'!$A:$A"), $C11, INDEX(INDIRECT("'"&$B11&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B11&"'!$A$2:$Z$2"), 0))),""))]1,832,097,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]538="Not Paid", XLOOKUP(C11,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C11,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3630-Sep-221711], $C$3:C12,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B12&"'!$A:$A"), $C12, INDEX(INDIRECT("'"&$B12&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B12&"'!$A$2:$U$2"), 0))), ""))]020,00028-Oct-22Late Payment Full], $C$3:C12,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B12&"'!$A:$A"), $C12, INDEX(INDIRECT("'"&$B12&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B12&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C12,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B12&"'!$A:$A"), $C12, INDEX(INDIRECT("'"&$B12&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B12&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C12,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C12,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-11.00
Bahria Boat Building Yard C-3631-Oct-220], $C$3:C13,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B13&"'!$A:$A"), $C13, INDEX(INDIRECT("'"&$B13&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B13&"'!$A$2:$U$2"), 0))), ""))]3,451,70040,00007-Nov-22On Time Partial], $C$3:C13,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B13&"'!$A:$A"), $C13, INDEX(INDIRECT("'"&$B13&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B13&"'!$A$2:$U$2"), 0))), ""))]102,913], $C$3:C13,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B13&"'!$A:$A"), $C13, INDEX(INDIRECT("'"&$B13&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B13&"'!$A$2:$Z$2"), 0))),""))]3,438,917,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]944="Not Paid", XLOOKUP(C13,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C13,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3631-Oct-220], $C$3:C14,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B14&"'!$A:$A"), $C14, INDEX(INDIRECT("'"&$B14&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B14&"'!$A$2:$U$2"), 0))), ""))]0100,00010-Nov-22On Time Full], $C$3:C14,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B14&"'!$A:$A"), $C14, INDEX(INDIRECT("'"&$B14&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B14&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C14,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B14&"'!$A:$A"), $C14, INDEX(INDIRECT("'"&$B14&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B14&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C14,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C14,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]4.001-30 days overdue
Bahria Boat Building Yard C-3631-Oct-220], $C$3:C15,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B15&"'!$A:$A"), $C15, INDEX(INDIRECT("'"&$B15&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B15&"'!$A$2:$U$2"), 0))), ""))]0100,00011-Nov-22On Time Full], $C$3:C15,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B15&"'!$A:$A"), $C15, INDEX(INDIRECT("'"&$B15&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B15&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C15,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B15&"'!$A:$A"), $C15, INDEX(INDIRECT("'"&$B15&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B15&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C15,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C15,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]3.001-30 days overdue
Bahria Boat Building Yard C-3631-Oct-220], $C$3:C16,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B16&"'!$A:$A"), $C16, INDEX(INDIRECT("'"&$B16&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B16&"'!$A$2:$U$2"), 0))), ""))]050,00014-Nov-22On Time Full], $C$3:C16,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B16&"'!$A:$A"), $C16, INDEX(INDIRECT("'"&$B16&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B16&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C16,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B16&"'!$A:$A"), $C16, INDEX(INDIRECT("'"&$B16&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B16&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C16,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C16,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]0.00
Bahria Boat Building Yard C-3630-Nov-22], $C$3:C17,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B17&"'!$A:$A"), $C17, INDEX(INDIRECT("'"&$B17&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B17&"'!$A$2:$U$2"), 0))), ""))]055,00005-Dec-22On Time Full], $C$3:C17,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B17&"'!$A:$A"), $C17, INDEX(INDIRECT("'"&$B17&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B17&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C17,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B17&"'!$A:$A"), $C17, INDEX(INDIRECT("'"&$B17&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B17&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C17,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C17,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]11.001-30 days overdue
Bahria Boat Building Yard C-3630-Nov-22], $C$3:C18,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B18&"'!$A:$A"), $C18, INDEX(INDIRECT("'"&$B18&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B18&"'!$A$2:$U$2"), 0))), ""))]040,00008-Dec-22On Time Full], $C$3:C18,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B18&"'!$A:$A"), $C18, INDEX(INDIRECT("'"&$B18&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B18&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C18,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B18&"'!$A:$A"), $C18, INDEX(INDIRECT("'"&$B18&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B18&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C18,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C18,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]8.001-30 days overdue
Bahria Boat Building Yard C-3630-Nov-22], $C$3:C19,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B19&"'!$A:$A"), $C19, INDEX(INDIRECT("'"&$B19&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B19&"'!$A$2:$U$2"), 0))), ""))]030,00016-Dec-22On Time Full], $C$3:C19,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B19&"'!$A:$A"), $C19, INDEX(INDIRECT("'"&$B19&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B19&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C19,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B19&"'!$A:$A"), $C19, INDEX(INDIRECT("'"&$B19&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B19&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C19,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C19,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]0.00
Bahria Boat Building Yard C-3631-Jan-23], $C$3:C20,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B20&"'!$A:$A"), $C20, INDEX(INDIRECT("'"&$B20&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B20&"'!$A$2:$U$2"), 0))), ""))]080,00013-Feb-23On Time Full], $C$3:C20,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B20&"'!$A:$A"), $C20, INDEX(INDIRECT("'"&$B20&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B20&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C20,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B20&"'!$A:$A"), $C20, INDEX(INDIRECT("'"&$B20&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B20&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C20,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C20,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3631-Jan-23], $C$3:C21,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B21&"'!$A:$A"), $C21, INDEX(INDIRECT("'"&$B21&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B21&"'!$A$2:$U$2"), 0))), ""))]040,00028-Feb-23Late Payment Full], $C$3:C21,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B21&"'!$A:$A"), $C21, INDEX(INDIRECT("'"&$B21&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B21&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C21,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B21&"'!$A:$A"), $C21, INDEX(INDIRECT("'"&$B21&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B21&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C21,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C21,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-8.00
Bahria Boat Building Yard C-3628-Feb-23], $C$3:C22,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B22&"'!$A:$A"), $C22, INDEX(INDIRECT("'"&$B22&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B22&"'!$A$2:$U$2"), 0))), ""))]060,00013-Mar-23On Time Full], $C$3:C22,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B22&"'!$A:$A"), $C22, INDEX(INDIRECT("'"&$B22&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B22&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C22,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B22&"'!$A:$A"), $C22, INDEX(INDIRECT("'"&$B22&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B22&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C22,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C22,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]7.001-30 days overdue
Bahria Boat Building Yard C-3631-Mar-230], $C$3:C23,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B23&"'!$A:$A"), $C23, INDEX(INDIRECT("'"&$B23&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B23&"'!$A$2:$U$2"), 0))), ""))]847,898100,00030-Apr-23Late Payment Partial], $C$3:C23,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B23&"'!$A:$A"), $C23, INDEX(INDIRECT("'"&$B23&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B23&"'!$A$2:$U$2"), 0))), ""))]133,867], $C$3:C23,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B23&"'!$A:$A"), $C23, INDEX(INDIRECT("'"&$B23&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B23&"'!$A$2:$Z$2"), 0))),""))]837,346,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]12,560="Not Paid", XLOOKUP(C23,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C23,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-13.00
Bahria Boat Building Yard C-3630-Apr-230], $C$3:C24,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B24&"'!$A:$A"), $C24, INDEX(INDIRECT("'"&$B24&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B24&"'!$A$2:$U$2"), 0))), ""))]889,707120,00008-May-23On Time Partial], $C$3:C24,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B24&"'!$A:$A"), $C24, INDEX(INDIRECT("'"&$B24&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B24&"'!$A$2:$U$2"), 0))), ""))]130,591], $C$3:C24,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B24&"'!$A:$A"), $C24, INDEX(INDIRECT("'"&$B24&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B24&"'!$A$2:$Z$2"), 0))),""))]878,489,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]159="Not Paid", XLOOKUP(C24,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C24,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]11.001-30 days overdue
Bahria Boat Building Yard C-3631-May-230], $C$3:C25,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B25&"'!$A:$A"), $C25, INDEX(INDIRECT("'"&$B25&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B25&"'!$A$2:$U$2"), 0))), ""))]896,61635,00020-Jun-23Late Payment Partial], $C$3:C25,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B25&"'!$A:$A"), $C25, INDEX(INDIRECT("'"&$B25&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B25&"'!$A$2:$U$2"), 0))), ""))]115,363], $C$3:C25,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B25&"'!$A:$A"), $C25, INDEX(INDIRECT("'"&$B25&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B25&"'!$A$2:$Z$2"), 0))),""))]885,070,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]13,276="Not Paid", XLOOKUP(C25,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C25,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-12.00
Bahria Boat Building Yard C-3631-May-230], $C$3:C26,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B26&"'!$A:$A"), $C26, INDEX(INDIRECT("'"&$B26&"'!$A:$U"), 0, MATCH(E$2, INDIRECT("'"&$B26&"'!$A$2:$U$2"), 0))), ""))]0115,00020-Jun-23Late Payment Full], $C$3:C26,[@[Billing Period]]) > 1, 0, IFERROR(SUMIF(INDIRECT("'"&$B26&"'!$A:$A"), $C26, INDEX(INDIRECT("'"&$B26&"'!$A:$U"), 0, MATCH(I$2, INDIRECT("'"&$B26&"'!$A$2:$U$2"), 0))), ""))]0], $C$3:C26,[@[Billing Period]]) > 1, 0,IFERROR(SUMIF(INDIRECT("'"&$B26&"'!$A:$A"), $C26, INDEX(INDIRECT("'"&$B26&"'!$A:$Z"), 0, MATCH(J$2, INDIRECT("'"&$B26&"'!$A$2:$Z$2"), 0))),""))]0,$R$2:$T$6,2,FALSE),0, [@Total]*0.015,([@Amount]-[@[Received Amt]])*0.015,[@Total]*0.015,[@Total]*0.015),"")]0="Not Paid", XLOOKUP(C26,$O$8:$O$32,$Q$8:$Q$32)-TODAY(), XLOOKUP(C26,$O$8:$O$32,$Q$8:$Q$32)-[@[Payment Date]])]-12.00
#N/A
#N/A
#N/A
#N/A
#N/A
Total29
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
111
aurelius33
A
J
  • Solved
Simpler way to calculate uneven payments over time
Replies
3
Views
300
juanbolas
J
MrDB4Excel
  • Question Question
Refund or Payment
Replies
2
Views
493
MrDB4Excel
MrDB4Excel
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
C
  • Question Question
3 payment some payments not made or paid in full
Replies
1
Views
543
CrashOD
C

Forum statistics

Threads
1,224,812
Messages
6,181,098
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top