你会跨工作簿统计吗？

粉丝留言，跨表用SUMIF被坑过，这里的表指工作簿。跨工作簿要打开所有工作簿才能引用正确，否则是错误值，确实坑人。

另外一位粉丝想学条件计数。

这两个留言，卢子就一起说了，文章有点长，请用心看完。

大多数情况下，我们对表格统计都会在同一张工作簿进行，这样操作起来非常方便，不过有时也会进行跨工作簿统计。

跨工作簿条件求和最常见的两个问题：

01跨工作簿区域应该如何写？

02关闭工作簿后，统计出来的结果变成错误值怎么回事？

条件求和，首先想到的是用SUMIF函数进行统计。

函数语法：

=SUMIF(条件区域,条件,求和区域)

其实跨工作簿的情况下，区域的选取跟在同一个工作簿一样，都是用鼠标选取，而不是手写。有一点必须要记住：两个工作簿必须同时打开。

鼠标选取区域详见动画

最终公式为：

=SUMIF([跨工作簿统计1.xlsx]Sheet1!$B:$B,A2,[跨工作簿统计1.xlsx]Sheet1!$D:$D)

一旦将跨工作簿统计1.xlsx关闭，修改统计月份，金额就变成错误值。

在Excel中，并不是所有函数都支持跨工作簿，如SUMIF、COUNTIF函数就不支持，而VLOOKUP、SUMPRODUCT函数就支持。这里可以借助SUMPRODUCT函数实现跨工作簿统计。

函数语法：

=SUMPRODUCT((条件区域=条件)*求和区域)

我们重新看一下出错的单元格，公式变成：

=SUMIF('C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$B:$B,A2,'C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$D:$D)

C:\Users\chenxilu\Desktop这个是路径，意思就是说这个表格存在卢子电脑的桌面。

[跨工作簿统计1.xlsx]Sheet1这个是工作簿名称跟工作表名称。

不要看公式很长，其实拆分开真的没什么，都是很简单的东西。

SUMPRODUCT函数不支持引用整列，这里只要将原来的区域改小，稍作变动就完成了最终的统计。

=SUMPRODUCT(('C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$B$2:$B$100=A2)*'C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$D$2:$D$100)

这样即使工作簿不打开的情况下，也能正确统计。

SUMPRODUCT函数也可以换成SUM函数，不过需要按Ctrl+Shift+Enter三键结束。

=SUM(('C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$B$2:$B$100=A2)*'C:\Users\chenxilu\Desktop\[跨工作簿统计1.xlsx]Sheet1'!$D$2:$D$100)

这2个函数90%的情况下可以互相代替，用SUMPRODUCT函数的好处就是支持数组公式，不用按三键。

再说一个特殊案例，就是在输入数据的时候，可能会中间出入文本，多敲个空格之类的，如果直接求和会出错。

用*这种方法，文本*数字就是错误值，不管用SUMPRODUCT函数还是SUM函数都无法避免出错。

不过SUMPRODUCT函数还隐藏了另外一种用法，参数用,（逗号）隔开，可以将文本当做0处理。

=SUMPRODUCT(--(MONTH($A$2:$A$26)=F2),$D$2:$D$26)

语法：

=SUMPRODUCT(数字区域1,数字区域2,数字区域3)

这里的(MONTH($A$2:$A$26)=F2)得到的是逻辑值，并不是数字，所以用--转换成数字1、0，从而可以正确求和。

关于条件计数，清风徐来以前写过一篇COUNTIF与SUMPRODUCT函数过招！

1.下面请看第一场比赛：如何统计值班经理的值班次数？

COUNTIF函数首先应战，在H2单元格输入公式，并向下填充。

=COUNTIF(A:A,G2)

COUNTIF函数语法：

=COUNTIF(条件区域,条件)

SUMPRODUCT函数也不甘示弱，在I2单元格输入公式，并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*1)

SUMPRODUCT函数单条件计数语法：

=SUMPRODUCT((条件1)*1)

或者

=SUMPRODUCT(--(条件1))

2.第一场比赛可谓势均力敌，不分胜负。下面请看第二场比赛：统计值班经理在中午时间段的值班次数。

两个条件？COUNTIF函数顿时傻眼了，多条件计数是COUNTIF函数心里永远的痛。然而，SUMPRODUCT函数却气定神闲，在H2单元格输入公式，并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*($B$2:$B$10=$H$1))

SUMPRODUCT函数多条件计数语法：

=SUMPRODUCT((条件1)*(条件2)*(条件n))

“打虎亲兄弟，上阵父子兵”，看到兄弟COUNTIF函数有难，擅长多条件计数的COUNTIFS函数果断出手了，在I2单元格输入公式，并向下填充。

=COUNTIFS(A:A,G2,B:B,$H$1)

COUNTIFS函数语法：

=COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)

3.第二场比赛的结果大家有目共睹，胜利属于SUMPRODUCT函数。下面请看第三场比赛：值班经理都用了一个字作为自己的简称，如何根据简称统计值班次数？

SUMPRODUCT函数没有了之前的淡定从容，陷入了沉思中。而COUNTIF函数却露出了久违的笑容，它拿出了自己的绝活，在H2单元格输入公式，并向下填充。

=COUNTIF(A:A,"*"&G2&"*")

在这里，“*”代表通配符，表示任意一个或者多个字符。在Excel函数中，能与通配符配合使用的函数并不多，COUNTIF函数是其中的一个，当然也包括了COUNTIFS函数，SUMIF函数，SUMIFS函数，VLOOKUP函数，MATCH函数等等。

SUMPRODUCT函数想破了脑袋，借助其他函数，终于也统计出来了。

=SUMPRODUCT(--ISNUMBER(FIND(G2,$A$2:$A$10)))

这个公式比较复杂，下面我们按步骤来分析。

Step 01先看最里层的FIND函数， FIND函数的语法：

=FIND(查找的字符,查找的地方)

在I2，I3单元格分别输入公式:

=FIND("风","风清扬")

=FIND("风","东方不败")

在第一个公式中，因为字符“风”在字符串“风清扬”的第一个位置，所以结果返回1。而第二个公式中，因为字符“风”没有在字符串“东方不败”中，所以结果返回错误值。

Step 02熟悉了FIND函数的基本运用后，我们在I2单元格输入公式：

=FIND(G2,$A$2:$A$10)

我们知道，在“A2:A10”区域中，存在了两个“风清扬”，按道理，字符“风”是能查找到的，应该返回数字才对啊，但是却返回错误值，这究竟是为什么呢？

FIND函数的第二个参数是一个区域，所以返回的结果是若干个数据，多个数据放在一个单元格中，当然会出错了。这个时候，我们需要借助一个神器：独孤九剑，也就是F9键。选择公式所在单元格，点击编辑栏，按F9键。

Step 03带有红色方框的数字个数就代表了该值班经理的值班次数。那么怎么统计数字的个数呢？可以使用ISNUMBER函数，如果是数字就返回TRUE，否则就返回FALSE。

=ISNUMBER(FIND(G2,$A$2:$A$10))

Step 04再结合SUMPRODUCT函数，结果便出来了，公式在上面已经给出。

第三场比赛，虽然SUMPRODUCT函数最后完成了任务，但评委的眼睛是雪亮的，这一次，评委把票投给了COUNTIF函数。

比赛的结果并不重要，重要的是，在什么时候该使用什么函数，按卢神的说法就是：怎么简单怎么来，作为这次比赛的吃瓜观众，你们说呢？

你要像清风徐来一样厉害吗？

恭喜这3位粉丝：KK、smile、Liu zg (则喜)，获得书籍《Excel跟卢子一起学 早做完，不加班》，加卢子微信chenxilu2019

VIP 888 元，所有 视频课程 ，终生免费学，提供一年在线答疑服务。

报名后加卢子微信chenxilu2019，发送报名截图邀请进群。

推荐：万般皆套路！Excel中让你爽到爆的查找、求和套路

上篇：SUMPRODUCT函数自称求和之王，SUMIFS不服气要来PK

你还想看什么函数PK？

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）