求指导，单元格选项，其他单元格求和数量变化

daboluo0426

solextrade

=SUMPRODUCT((MMULT(SIGN(B$2:B$10-MID(E2,FIND("码",E2)-{2,-2},2)),{1;1})^2<2)*C$2:C$10)*ISNUMBER(FIND(LEFT(E2),H$2))

良良

选中F2:F4，区域数组：
=IF(ISNUMBER(FIND(LEFT(E2,2),H$2)),MMULT(IF(ISNUMBER(VDB(,,{20;30;41},{11;21;31},TRANSPOSE(B$2:B$10))),TRANSPOSE(C$2:C$10),),ROW($1:$9)^0),"")，下拉至F10。

daboluo0426

solextrade 发表于 2023-6-16 16:34
=SUMPRODUCT((MMULT(SIGN(B$2:B$10-MID(E2,FIND("码",E2)-{2,-2},2)),{1;1})^2

谢谢大神，真心感谢你，怎么复杂的公式都能写出来，学习了

daboluo0426

良良 发表于 2023-6-16 16:44
选中F2:F4，区域数组：
=IF(ISNUMBER(FIND(LEFT(E2,2),H$2)),MMULT(IF(ISNUMBER(VDB(,,{20;30;41},{11;21; ...

谢谢大神，真心感谢你，怎么复杂的公式都能写出来，学习了。

袁勇

=SUM(SUMIF(B:B,{"<","<="}&MID(E2,FIND("码",E2)-{2,-2},2),C:C)*{-1,1})*ISNUMBER(FIND(LEFT(E2),H$2))

daboluo0426

solextrade 发表于 2023-6-16 16:34
=SUMPRODUCT((MMULT(SIGN(B$2:B$10-MID(E2,FIND("码",E2)-{2,-2},2)),{1;1})^2

大神，刚才那个公式中间查询值没研究透彻，我修改了条件，麻烦在帮改改，谢谢

quqiyuan

=IF(LEFT(E2,2)=$H$2,SUMIFS(C:C,B:B,">="&MID(E2,MIN(IFERROR(FIND(ROW($1:$10)-1,E2),4^10)),2),B:B,"<="&MID(E2,FIND("-",E2)+1,2)),"")

solextrade

daboluo0426 发表于 2023-6-16 17:26
大神，刚才那个公式中间查询值没研究透彻，我修改了条件，麻烦在帮改改，谢谢

=SUMIF(B:B,"<="&MID(E2,3,2),C:C)*COUNTIF(E2,H$2&"*")

ericxzhou

=IF(LEFT(E2,2)=$H$2,SUM(FILTER($C$2:$C$10,($B$2:$B$10>=--RIGHT(LEFT(E2,FIND("码",E2)-1),2))*($B$2:$B$10<=--LEFT(RIGHT(E2,3),2)))),"")