大多數(shù)情況下，我們對表格統(tǒng)計(jì)都會在同一張工作簿進(jìn)行，這樣操作起來非常方便，不過有時也會進(jìn)行跨工作簿統(tǒng)計(jì)。

跨工作簿條件求和最常見的兩個問題：

01 跨工作簿區(qū)域應(yīng)該如何寫？

02 關(guān)閉工作簿后，統(tǒng)計(jì)出來的結(jié)果變成錯誤值怎么回事？

條件求和，首先想到的是用SUMIF函數(shù)進(jìn)行統(tǒng)計(jì)。

函數(shù)語法：

=SUMIF(條件區(qū)域,條件,求和區(qū)域)

其實(shí)跨工作簿的情況下，區(qū)域的選取跟在同一個工作簿一樣，都是用鼠標(biāo)選取，而不是手寫。有一點(diǎn)必須要記住：兩個工作簿必須同時打開。

鼠標(biāo)選取區(qū)域詳見動畫

最終公式為：

=SUMIF([跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B:$B,A2,[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D:$D)

一旦將跨工作簿統(tǒng)計(jì)1.xlsx關(guān)閉，修改統(tǒng)計(jì)月份，金額就變成錯誤值。

在Excel中，并不是所有函數(shù)都支持跨工作簿，如SUMIF、COUNTIF函數(shù)就不支持，而VLOOKUP、SUMPRODUCT函數(shù)就支持。這里可以借助SUMPRODUCT函數(shù)實(shí)現(xiàn)跨工作簿統(tǒng)計(jì)。

函數(shù)語法：

=SUMPRODUCT((條件區(qū)域=條件)*求和區(qū)域)

我們重新看一下出錯的單元格，公式變成：

=SUMIF(C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B:$B,A2,C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D:$D)

C:UserschenxiluDesktop這個是路徑，意思就是說這個表格存在盧子電腦的桌面。

[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1這個是工作簿名稱跟工作表名稱。

不要看公式很長，其實(shí)拆分開真的沒什么，都是很簡單的東西。

SUMPRODUCT函數(shù)不支持引用整列，這里只要將原來的區(qū)域改小，稍作變動就完成了最終的統(tǒng)計(jì)。

=SUMPRODUCT((C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B$2:$B$100=A2)*C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D$2:$D$100)

這樣即使工作簿不打開的情況下，也能正確統(tǒng)計(jì)。

SUMPRODUCT函數(shù)也可以換成SUM函數(shù)，不過需要按Ctrl Shift Enter三鍵結(jié)束。

=SUM((C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B$2:$B$100=A2)*C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D$2:$D$100)

這2個函數(shù)90%的情況下可以互相代替，用SUMPRODUCT函數(shù)的好處就是支持?jǐn)?shù)組公式，不用按三鍵。

再說一個特殊案例，就是在輸入數(shù)據(jù)的時候，可能會中間出入文本，多敲個空格之類的，如果直接求和會出錯。

用*這種方法，文本*數(shù)字就是錯誤值，不管用SUMPRODUCT函數(shù)還是SUM函數(shù)都無法避免出錯。

不過SUMPRODUCT函數(shù)還隱藏了另外一種用法，參數(shù)用,（逗號）隔開，可以將文本當(dāng)做0處理。

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

語法：

=SUMPRODUCT(數(shù)字區(qū)域1,數(shù)字區(qū)域2,數(shù)字區(qū)域3)

這里的(MONTH($A$2:$A$26)=F2)得到的是邏輯值，并不是數(shù)字，所以用--轉(zhuǎn)換成數(shù)字1、0，從而可以正確求和。

關(guān)于條件計(jì)數(shù)，可以看看COUNTIF與SUMPRODUCT這倆函數(shù)過招！

1.下面請看第一場比賽：如何統(tǒng)計(jì)值班經(jīng)理的值班次數(shù)？

COUNTIF函數(shù)首先應(yīng)戰(zhàn)，在H2單元格輸入公式，并向下填充。

=COUNTIF(A:A,G2)

COUNTIF函數(shù)語法：

=COUNTIF(條件區(qū)域,條件)

SUMPRODUCT函數(shù)也不甘示弱，在I2單元格輸入公式，并向下填充。

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

SUMPRODUCT函數(shù)單條件計(jì)數(shù)語法：

=SUMPRODUCT((條件1)*1)

或者

=SUMPRODUCT(--(條件1))

2.第一場比賽可謂勢均力敵，不分勝負(fù)。下面請看第二場比賽：統(tǒng)計(jì)值班經(jīng)理在中午時間段的值班次數(shù)。

兩個條件？COUNTIF函數(shù)頓時傻眼了，多條件計(jì)數(shù)是COUNTIF函數(shù)心里永遠(yuǎn)的痛。然而，SUMPRODUCT函數(shù)卻氣定神閑，在H2單元格輸入公式，并向下填充。

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

SUMPRODUCT函數(shù)多條件計(jì)數(shù)語法：

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

“打虎親兄弟，上陣父子兵”，看到兄弟COUNTIF函數(shù)有難，擅長多條件計(jì)數(shù)的COUNTIFS函數(shù)果斷出手了，在I2單元格輸入公式，并向下填充。

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

COUNTIFS函數(shù)語法：

=COUNTIFS(條件區(qū)域1,條件1,條件區(qū)域2,條件2,條件區(qū)域n,條件n)

3.第二場比賽的結(jié)果大家有目共睹，勝利屬于SUMPRODUCT函數(shù)。下面請看第三場比賽：值班經(jīng)理都用了一個字作為自己的簡稱，如何根據(jù)簡稱統(tǒng)計(jì)值班次數(shù)？

SUMPRODUCT函數(shù)沒有了之前的淡定從容，陷入了沉思中。而COUNTIF函數(shù)卻露出了久違的笑容，它拿出了自己的絕活，在H2單元格輸入公式，并向下填充。

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

在這里，“*”代表通配符，表示任意一個或者多個字符。在Excel函數(shù)中，能與通配符配合使用的函數(shù)并不多，COUNTIF函數(shù)是其中的一個，當(dāng)然也包括了COUNTIFS函數(shù)，SUMIF函數(shù)，SUMIFS函數(shù)，VLOOKUP函數(shù)，MATCH函數(shù)等等。

SUMPRODUCT函數(shù)想破了腦袋，借助其他函數(shù)，終于也統(tǒng)計(jì)出來了。

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

這個公式比較復(fù)雜，下面我們按步驟來分析。

Step 01 先看最里層的FIND函數(shù)， FIND函數(shù)的語法：

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

在I2，I3單元格分別輸入公式:

=FIND("風(fēng)","風(fēng)清揚(yáng)")

=FIND("風(fēng)","東方不敗")

在第一個公式中，因?yàn)樽址帮L(fēng)”在字符串“風(fēng)清揚(yáng)”的第一個位置，所以結(jié)果返回1。而第二個公式中，因?yàn)樽址帮L(fēng)”沒有在字符串“東方不敗”中，所以結(jié)果返回錯誤值。

Step 02 熟悉了FIND函數(shù)的基本運(yùn)用后，我們在I2單元格輸入公式：

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

我們知道，在“A2:A10”區(qū)域中，存在了兩個“風(fēng)清揚(yáng)”，按道理，字符“風(fēng)”是能查找到的，應(yīng)該返回?cái)?shù)字才對啊，但是卻返回錯誤值，這究竟是為什么呢？

FIND函數(shù)的第二個參數(shù)是一個區(qū)域，所以返回的結(jié)果是若干個數(shù)據(jù)，多個數(shù)據(jù)放在一個單元格中，當(dāng)然會出錯了。這個時候，我們需要借助一個神器：獨(dú)孤九劍，也就是F9鍵。選擇公式所在單元格，點(diǎn)擊編輯欄，按F9鍵。

Step 03 帶有紅色方框的數(shù)字個數(shù)就代表了該值班經(jīng)理的值班次數(shù)。那么怎么統(tǒng)計(jì)數(shù)字的個數(shù)呢？可以使用ISNUMBER函數(shù)，如果是數(shù)字就返回TRUE，否則就返回FALSE。

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

Step 04 再結(jié)合SUMPRODUCT函數(shù)，結(jié)果便出來了，公式在上面已經(jīng)給出。

第三場比賽，雖然SUMPRODUCT函數(shù)最后完成了任務(wù)，但評委的眼睛是雪亮的，這一次，評委把票投給了COUNTIF函數(shù)。

比賽的結(jié)果并不重要，重要的是，在什么時候該使用什么函數(shù)，怎么簡單怎么來，作為這次比賽的吃瓜觀眾，你們說呢？

本文來源：Excel不加班，稅小課整理發(fā)布，文章版權(quán)歸原作者所有，如有不妥，請聯(lián)系刪除。