以下公式能自动适应表格1以后有数据新增的情况
I2公式右拉:
=TEXT(INDEX($A:$A,SMALL(IF(MATCH($A$2:$A$999&"",$A$2:$A$999&"",)=ROW($1:$998),ROW($2:$999),4^8),COLUMN(A1)))&"","e-m-d")
G3公式右拉至H3再下拉:
=INDEX(B:B,SMALL(IF(MATCH($B$2:$B$999&$C$2:$C$999,$B$2:$B$999&$C$2:$C$999,)=ROW($1:$998),ROW($2:$999),4^8),ROW(A1)))&""
上面两条公式是数组公式,输入完成后不要直接回车,要按三键 CTRL+SHIFT+回车 结束。
I3公式右拉再下拉:
=IF(($G3="")+(I$2=""),"",SUMPRODUCT(($B$2:$B$999&$C$2:$C$999=$G3&$H3)*($A$2:$A$999=--I$2)*$E$2:$E$999))