總獎金計算 無輔助 利用表列 可更改 (12810)

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)



留言