你會跨工作簿統計嗎？

粉絲留言，跨表用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）