Question

求EXCEL中多种条件下的数据汇总求和公式

如图，求在符合期间在开始确认收入时间与结束确认时间之间，并且产品符合的收入汇总及项目数量统计。

Answer 1

C12单元格输入以下公式

=SUMIFS($G$2:$G$6,$B$2:$B$6,B12,$D$2:$D$6,"<="&A12,E2:E6,">="&A12)

D12单元格输入以下公式

=COUNTIFS($B$2:$B$6,B12,$D$2:$D$6,"<="&A12,$E$2:$E$6,">="&A12)

Answer 2

C12公式：
=SUMIFS(G:G,D:D,"<="&A12,E:E,">="&A12,B:B,B12)
D12公式：
=COUNTIFS(D:D,"<="&A12,E:E,">="&A12,B:B,B12)

Answer 3

LOOKUP函数多条件查询套路为：=LOOKUP(1,0/(条件)/(条件2)/(条件n),查询区域)