根据指定范围的数字条件精确查找并求和

Cherish87 · 发表于 2024-7-10 10:04

如图片我想求标黄色单元格中的数值的和，正常情况就是标橙色部分的合计，但是使用公式=SUM(IF(ISNUMBER(FINDB(C:C,F5)),D:D))时会把未标颜色的数值金额也进行合计，对此如何进行精确求和

hugeinfo · 发表于 2024-7-10 10:08

=SUMPRODUCT(ISNUMBER(FIND(C4:C8,F5))*D4:D8)

qwer401245 · 发表于 2024-7-10 10:09

=SUM(ISNUMBER(FIND(","&C4:C8&",",","&F5&","))*D4:D8)

grayw · 发表于 2024-7-10 10:17

=SUMIF(C:C,"<>1620190",D:D)

mjm04 · 发表于 2024-7-10 10:25

=SUM(MAP(TEXTSPLIT(F5,,","),LAMBDA(a,SUMIFS(D4:D8,C4:C8,a))))

inrever · 发表于 2024-7-10 10:26

复制代码

Cherish87 · 发表于 2024-7-10 11:25

qwer401245 发表于 2024-7-10 10:09
=SUM(ISNUMBER(FIND(","&C4:C8&",",","&F5&","))*D4:D8)

谢谢，想问一下","",",","","的作用是什么呀

qwer401245 · 发表于 2024-7-10 11:28

Cherish87 发表于 2024-7-10 11:25
谢谢，想问一下","",",","","的作用是什么呀

分隔定位让匹配精确

我的城市你来过 · 发表于 2024-7-14 16:25

=SUM(FILTER(D4:D8,MMULT(ISNUMBER(FIND(REGEXP($F$5,"\d+"),$C$4:$C$8))/1,ROW(A$1:A$4)^0)=1))

		自动登录	找回密码
密码			免费注册

[求助] 根据指定范围的数字条件精确查找并求和