Question

EXCEL公式问题，请大神救命？

如图所示，需要根据零件的发货地点和重量区间，分别查询到相对应的运费和提货费，因为发货地点有相同的名称，只是后面根据不同的重量区间，运费有所不同，还请各位帮帮忙

Answer 1

C5输入

=SUMIFS(L:L,H:H,C4,I:I,"<="&F9,J:J,">"&F9)

C6输入

=SUMIFS(M:M,H:H,C4,I:I,"<="&F9,J:J,">"&F9)

均回车。

Answer 2

Excel公式如下 C5:=SUMPRODUCT((H5:H16=C4)*((I5:I16<=F9)+(J5:J16>=F9))*L5:L16)C6:=SUMPRODUCT((H5:H16=C4)*((I5:I16<=F9)+(J5:J16>=F9))*M5:M16)

追问

Answer 3

C5:=SUMPRODUCT((H5:H16=C4)*((I5:I16<=F9)+(J5:J16>=F9))*L5:L16)
C6:=SUMPRODUCT((H5:H16=C4)*((I5:I16<=F9)+(J5:J16>=F9))*M5:M16)