求教大神有没有公式可以实现附件里的效果

wenbai0626

附件里面有两个表，一个是数据源，一个是想要实现的结果，主要是想根据sheet1里面的日期和姓名结合其他的条件汇总出符合条件的量，麻烦大神看下有没有什么公式可以符合条件，非常感谢

miaojohn45

COUNTIFS函数即可处理。

海马爸爸

D2=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!A:A,">"&B2,Sheet1!A:A,"<"&C2+1,Sheet1!C:C,"<>""")
E2=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!A:A,">"&B2,Sheet1!A:A,"<"&C2+1,Sheet1!C:C,"是")
F2=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!A:A,">"&B2,Sheet1!A:A,"<"&C2+1,Sheet1!C:C,"否")
G2=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!A:A,">"&B2,Sheet1!A:A,"<"&C2+1,Sheet1!D:D,"4")
H2=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!A:A,">"&B2,Sheet1!A:A,"<"&C2+1,Sheet1!D:D,"5")

mykitami

D2=NPV(-2,COUNTIFS(Sheet1!B:B,Sheet2!$A2,Sheet1!A:A,"<="&Sheet2!$B2:$C2+{-1,0}))
E2=NPV(-2,COUNTIFS(Sheet1!B:B,Sheet2!$A2,Sheet1!A:A,"<="&Sheet2!$B2:$C2+{-1,0},Sheet1!C:C,"是"))
F2=NPV(-2,COUNTIFS(Sheet1!B:B,Sheet2!$A2,Sheet1!A:A,"<="&Sheet2!$B2:$C2+{-1,0}))
G2=NPV(-2,COUNTIFS(Sheet1!B:B,Sheet2!$A2,Sheet1!A:A,"<="&Sheet2!$B2:$C2+{-1,0},Sheet1!D:D,"4"))
H2=NPV(-2,COUNTIFS(Sheet1!B:B,Sheet2!$A2,Sheet1!A:A,"<="&Sheet2!$B2:$C2+{-1,0},Sheet1!D:D,"5"))

Holiday2022

第1个条件和第3个条件，有什么区别吗？