D列和E列按A列同名称求和

杨玉兴

如题，D列和E列按A列同名称求和，请帮忙解决一下

中药库存.zip (27.31 KB, 下载次数: 10)

2021-11-17 09:22 上传

点击文件名下载附件
中药库存.zip

赵定仙

=SUM(($A$2:$A$442=$H2)*IF(ISNUMBER(FIND("kg",D$2:D$442)),NUMBERVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(D$2:D$442,"kg",REPT(" ",20)),"g",REPT(" ",20)),1,20)))*1000+NUMBERVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(D$2:D$442,"kg",REPT(" ",20)),"g",REPT(" ",20)),21,20))),--LEFT(D$2:D$442,LEN(D$2:D$442)-1)))

数组公式，右拉下拉

莫笑腊酒浑

感谢感谢，

林向荣

=SUM(($A$2:$A$442=$H2)*IF(ISNUMBER(FIND("kg",D$2:D$442)),MMULT(NUMBERVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D$2:D$442,"kg",REPT(" ",20)),"g",REPT(" ",20)),"条",REPT(" ",20)),"个",REPT(" ",20)),{1,21},20))),{1000;1}),--LEFT(D$2:D$442,LEN(D$2:D$442)-1)))

本来想简化一下公式，发现你除了KG,G还有条和个。。。

寡言呱唧

这些问题，本来是可以在表单的设计上规避的，非要搞成一坨，何必为难自己呢，

提刀夜行

没有困难，创造困难

华山再现

这是系统里面导出来的，

海军欧巴

=SUM(MMULT(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(IF(ISNUMBER(FIND("kg",$D$2:$D$442)),$D$2:$D$442,"kg"&$D$2:$D$442),"kg",REPT(" ",99)),COLUMN(A:B)*99-98,99),"g",)*{1000,1},),ROW($1:$2)^0)*($A$2:$A$442=H2))