1、已收金额。点击F11输入公式：=IFS(SUMIFS($R$11:$R$500,$O$11:$O$500,C11)>=D11,D11,SUMIFS($R$11:$R$500,$O$11:$O$500,C11)<=D11,SUMIFS($R$11:$R$500,$O$11:$O$500,C11))
3、未收金额。点击G11输入公式：=D11-F11到期日期。点击H11输入公式：=IF(B11="","-",B11+E11)
4、是否到期。点击I11输入：=IFERROR(IF(TODAY()-H11>0,"是","否"),"-")逾期0-30天。点击J11输入：=IFERROR(IF(AND(TODAY()-H11>0,TODAY()-H11<=30),G11,0),"-")
5、逾期30-60天。点击K11输入：=IFERROR(IF(AND(TODAY()-H11>30,TODAY()-H11<=60),G11,0),"-")逾期60-90天。点击L11输入：=IFERROR(IF(AND(TODAY()-H11>60,TODAY()-H11<=90),G11,0),"-")逾期90天以上。点击M11输入：=IFERROR(IF(TODAY()-H11>90,G11,0),"-")
7、汇总表。C4单元格输入：=SUMIFS($J$11:$J$500,$C$11:$C$500,B4)C5单元格输入：=SUMIFS($J$11:$J$500,$C$11:$C$500,B5)C6单元格输入：=SUMIFS($J$11:$J$500,$C$11:$C$500,B6)
