|
C列=COUNTIFS(数据!G:G,计算!A3,数据!D:D,">=2017/01/01",数据!D:D,"<"&DATE(YEAR(计算!A3),MONTH(计算!A3),1))D列=SUMPRODUCT((数据!G$2:G$26=计算!A3)*(数据!D$2:D$26<DATE(2017,1,1))*数据!C$2:C$26)
E列=COUNTIFS(数据!G:G,计算!A3,数据!D:D,">=2017/01/01",数据!D:D,"<"&DATE(YEAR(计算!A3),MONTH(计算!A3),1))
F列=SUMPRODUCT((数据!G$2:G$26=计算!A3)*(数据!D$2:D$26>=DATE(2017,1,1))*(数据!D$2:D$26<=DATE(YEAR(计算!A3),MONTH(计算!A3),1))*(数据!C$2:C$26))
G列=COUNTIFS(数据!G:G,计算!A3,数据!D:D,">="&DATE(2017,MONTH(A3),1),数据!D:D,"<"&DATE(YEAR(计算!A3),MONTH(计算!A3)+1,1))
H列=SUMPRODUCT((数据!G$2:G$26=计算!A3)*(数据!D$2:D$26>=DATE(2017,MONTH(A3),1))*(数据!D$2:D$26<DATE(YEAR(计算!A3),MONTH(计算!A3)+1,1))*(数据!C$2:C$26))
|
|