D2:D16{=LOOKUP(1,0/(IFERROR(C2-LEFT(F$1:F$4,FIND("~",F$1:F$4&"~")-1),)>=0),N(+G$1:G$4))
I6=SUM(D:D)
I2{=SUM(RIGHT(SMALL((IFERROR(TRANSPOSE(C2:C16)-LEFT(F$1:F$4,FIND("~",F$1:F$4&"~")-1),)>=0)*N(+G$1:G$4)+COLUMN(A:O)/1%%,ROW(1:15)*4),4)*1)
另解I2{=SUM(COUNTIFS(C:C,">="&LEFT(F$2:F$4,FIND("~",F$2:F$4&"~")-1)*1,C:C,"<="&IFERROR(MID(F$2:F$4,FIND("~",F$2:F$4&"~")+1,9)*1,10^9))*G2:G4)
留言
張貼留言