C3=SUM(FREQUENCY(LEFT(A3,FIND("/",A3)-1)+{0,1},{5.9,9})*INDEX(CHOOSE({1,2,3},H$4:H$9,I$4:I$9,H$4:H$9),LOOKUP(B3,{0;241;661;1001;1401;2001},ROW($1:$6)),N(IF({1},{1;2;3})))*B3/2)
另解=SUMPRODUCT(CHOOSE(LOOKUP(LEFT(A3,FIND("/",A3)-1)*1,{1,5,6,9,10},ROW($1:$5)),{2,0},{1,1},{0,2},{1,1},{2,0})*OFFSET(H$3:I$3,LOOKUP(B3,{0;241;661;1001;1401;2001},ROW($1:$6)),))*B3/2
留言
張貼留言