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)

。

最后说⼀点我⾃⼰的感想，做完这个题，感觉函数⾃⼰没有学通，不会举⼀反三。⽐如下⾯这

个题，我会写函数，但是换了今天的题⽬我就不会了，吃饭时突然间有了灵感才想通的，这不

就是⼀回事嘛。