包含某字符的多条件求和

oklao5 · 发表于 2017-11-20 15:45

如何用excel函数或数组公式，计算包含关键字符的多条件求和？请高人赐教！谢了。

manageadmi · 发表于 2017-11-20 16:01

=SUM((A$18:A$43=A2)*(DATE(YEAR(B$18:B$43),MONTH(B$18:B$43),1)=B2)*(IF(ISERROR(FIND("住宿费",D$18:D$43)),0,1)+IF(ISERROR(FIND("误餐费",D$18:D$43)),0,1)+IF(ISERROR(FIND("差旅费",D$18:D$43)),0,1))*E$18:E$43)

复制代码

wofuocibei2008 · 发表于 2017-11-20 16:02

SUM(IFERROR(($A2=$A$18:$A$43)*(TEXT($B2,"YYYY-MM")=TEXT($B$18:$B$43,"yyyy-mm"))*(MMULT(IFERROR(FIND({"住宿费","误餐费","差旅费"},$D$18:$D$43),0),ROW(A1:A3))^0)*($E$18:$E$43),0))数组

gspl-xin · 发表于 2017-11-20 16:15

=SUM((A18:A43=A2)*(TEXT(B18:B43,"em")=TEXT(B2,"em"))*(IF(ISNUMBER(FIND({"住宿","误餐","差旅"}&"费",D18:D43)),1,0))*E18:E43)

复制代码

nsxlyj · 发表于 2017-11-20 16:15

=SUM(SUMIFS(E$18:E$43,A$18:A$43,A2,F$18:F$43,G2,D$18:D$43,"*"&{"住宿","误餐","差旅"}&"*"))数组公式，并借助辅助列

问山8828680 · 发表于 2017-11-20 16:26

SUMPRODUCT(($A$18:$A$800=A2)*($B$18:$B$800=B2)*(ISNUMBER(FIND({"住宿","误餐","差旅"},$D$18:$D$800)))*$E$18:$E$800)

oklao5 · 发表于 2017-11-21 09:27

gspl-xin 发表于 2017-11-20 16:15

难题解决，谢谢大神。

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

[求助] 包含某字符的多条件求和