sumifs中通配符之不包含关键字的用法

小伙伴们，大家好。今天来分享下sumifs中通配符不包含的用法。好像我们平时用到不包含的比较少，反正我是用的比较少，通常都是包含用的多。下面还是来看一个实例，来自于论坛一位朋友的问题。

求不同编号的仓库的总数量(不包含“报废”和“过期”的仓库)

下图左表是数据源，右表是求出的结果。以编号001为例说明，A列的编号是001，B列的仓库不包含关键字“报废”和“过期”，对C列的数量求和。也就是我用红色框标记出来的数量。这是个多条件求和的问题，你会怎么做呢？

我最开始的思路是用编号001的所有仓库的总数量减去编号001的包含“报废”仓的总数量，再减去编号001的包含“过期”仓的总数量，这也是常规的思路。所以公式为=SUM(SUMIFS(C:C,A:A,E3,B:B,{"*","*报废*","*过期*"})*{1,-1,-1})。

这是sumifs的数组用法，其中B列的条件是个常量数组，有3个元素，分别是"*","*报废*","*过期*"。"*"代表任意长度的文本，可以是空文本""，但不能代表空单元格和数字；"*报废*"代表任意包含“报废”2字的文本；"*过期*"代表任意包含“过期”2字的文本。

所以SUMIFS(C:C,A:A,E3,B:B,{"*","*报废*","*过期*"})就相当于对3种类型的仓库求总数量。分别是编号为001的所有仓库的总数量，编号为001的包含“报废”仓的总数量，编号为001的包含“过期”仓的总数量。

=SUM(SUMIFS(C:C,A:A,E3,B:B,{"*","*报废*","*过期*"})*{1,-1,-1})这部分乘以了{1,-1,-1}这样一个常量数组，其实就是用总的减去“报废”的，减去“过期”的。但是这个公式得到的结果是错误的。因为B7单元格既包含“报废”，又包含“过期”。这样的话就把B7单元格对应的数量减了2次。

改进后的公式为=SUMIFS(C:C,A:A,E3,B:B,"<>*报废*",B:B,"<>*过期*")。这样的话其实更简单直接，但是大多数人不会想到，可能不等于和通配符一起使用的教程比较少吧。意思就是A列的编号是001，并且B列的仓库既不包含“报废”，又不包含“过期”，对符合这些条件的记录求总数量。

其中"<>*报废*"的意思就是不等于包含“报废”2个字的字符串，简单来说就是不包含“报废”。这里还有一个多条件“且”的问题。比如B列的仓库既不包含“报废”，又不包含“过期”，就要写为=SUMIFS(C:C,A:A,E3,B:B,"<>*报废*",B:B,"<>*过期*")。

而不能写在一个数组中，比如=SUMIFS(C:C,A:A,E3,B:B,{"<>*报废*","<>*过期*"})这样，这样写的话是对不包含“报废”的求一个总数量，不包含“过期”的再求一个总数量，返回的结果有2个值。

多条件求和的话，除了sumifs外还可以用sum，sumproduct等。比如=SUMPRODUCT((MMULT(--ISERR(FIND({"报废","过期"},B$3:B$12)),{1;1})=2)*(A$3:A$12=E3)*C$3:C$12)也可以得到结果。

延续上一个公式的思路，还可以用替换函数substitute，公式为=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(B$3:B$12,"报废",),"过期",)=B$3:B$12)*(A$3:A$12=E3)*C$3:C$12)。

最后说一点我自己的感想，做完这个题，感觉函数自己没有学通，不会举一反三。比如下面这个题，我会写函数，但是换了今天的题目我就不会了，吃饭时突然间有了灵感才想通的，这不就是一回事嘛。

再比如，我会用mod计算出年份对应的生肖，如下图，却不会用mod求一个日期是星期几，其实这都是一回事

。我们一般求一个日期是周几用的weekday，其实用mod也可以，你可以先想一下，后面说到日期函数再说公式。

文件链接：

https://pan.baidu.com/s/1UOYzRP1jKsh5UYLgf2AgKQ

提取码：kg26