MIS Report in Account | Debtors and Creditors Report

 

MIS Report in Account | Debtors and Creditors Report

Download practice File - Click here

Learn more on this topic please Watch the Video

Formula use in this file

Data Sheet

Dash Board

Formula on Dash Board (Sheet1)

1. Calculate the Count Debtors

=COUNTIFS('TAT & Aging'!$F:$F,">="&Sheet1!C4,'TAT & Aging'!$F:$F,"<="&Sheet1!D4)

2. Calculate the Total Debtors

=IF(B7>=$B$4,"",B7+1)

3. Calculate the Days Left

{=IFERROR(LARGE(IF('TAT & Aging'!$F$2:$F$117>=Sheet1!$C$4,IF('TAT & Aging'!$F$2:$F$117<=Sheet1!$D$4,'TAT & Aging'!$F$2:$F$117)),B7),"")}

4. Calculate the Customer Name

=IF(B7>$B$4,"",INDEX('TAT & Aging'!$A$2:$A$117,AGGREGATE(15,6,(ROW('TAT & Aging'!$A$2:$A$117)-1)/('TAT & Aging'!$F$2:$F$117=Sheet1!D7),COUNTIF($D$7:D7,D7))))

5. Calculate the Amount

=IF(B7>$B$4,"",INDEX('TAT & Aging'!$C$2:$C$117,AGGREGATE(15,6,(ROW('TAT & Aging'!$A$2:$A$117)-1)/('TAT & Aging'!$F$2:$F$117=Sheet1!D7),COUNTIF($D$7:D7,D7))))

Learn more on this topic please Watch the Video

Download practice File - Click here