Excel 中最常用的匯總統(tǒng)計(jì)方式之一，就是求和。

每個(gè)公司對(duì)于求和的要求也不一樣。有的是對(duì)數(shù)據(jù)區(qū)域中的行進(jìn)行求和，有的對(duì)列數(shù)據(jù)進(jìn)行求和。還有的是對(duì)于行列交叉位置的數(shù)據(jù)進(jìn)行求和的，還有的是錯(cuò)位求和。

數(shù)據(jù)結(jié)構(gòu)也是千差萬(wàn)別。

這不，有位仁兄發(fā)來(lái)問(wèn)題求助，如下圖：

要求是：將其中的手續(xù)費(fèi)按照項(xiàng)目類(lèi)別分別統(tǒng)計(jì)求和。如下圖：

比如，常規(guī)培訓(xùn)手續(xù)費(fèi)，需要將【B】列常規(guī)培訓(xùn)單元格下面的手續(xù)費(fèi)都統(tǒng)計(jì)在內(nèi)。即將【D3】和【D5】的單元格求和。

場(chǎng)地租賃手續(xù)費(fèi)同理。

問(wèn)題分析

乍看到這個(gè)問(wèn)題時(shí)，我有點(diǎn)懵！

一般情況下我們可能會(huì)像下面這樣記錄數(shù)據(jù)。如下圖：

在手續(xù)費(fèi)之前加上歸屬的項(xiàng)目名稱(chēng)。

如果這樣記錄數(shù)據(jù)的話，就跟我們平時(shí)用求和函數(shù)的套路完全一樣了。

用到的公式如下：

• =SUMIF(B:B,F2,D:D)
  

公式解析：

在【B】列中，查找【F2】單元格的內(nèi)容，對(duì)符合條件的，在【D】列對(duì)應(yīng)單元格中的金額求和。

但是，現(xiàn)在的問(wèn)題是他們并不是像上面圖中那樣記錄數(shù)據(jù)。

如果直接用 SUMIFS 雙條件求和，結(jié)果會(huì)是下面這樣。

結(jié)果是 0！

公式如下：

• =SUMIFS(D:D,B:B,LEFT(F2,4),B:B,"手續(xù)費(fèi)")
  

問(wèn)題出在哪里呢？又到底該如何才能求和呢？

解決問(wèn)題

▋方法一：SUMIFS 多條件求和

根據(jù)上面的問(wèn)題分析來(lái)看，原始表格記錄成這樣，的確是一個(gè)雙條件求和的問(wèn)題。

就是這里的雙條件是一個(gè)錯(cuò)行求和。

我們將上面的公式改成如下這樣：

公式如下：

• =SUMIFS($D$3:$D$8,$B$2:$B$7,LEFT(F2,4),$B$3:$B$8,"手續(xù)費(fèi)")
  

公式解析：

SUMIFS 多條件求和這里有兩個(gè)條件，

第一個(gè)條件對(duì)是：「 $B$2:$B$7,LEFT(F2,4) ?」。

在單元格【$B$2:$B$7】中，查找【F2】單元格中的前四個(gè)字符的內(nèi)容。

即：常規(guī)培訓(xùn)。（這里用 LEFT 函數(shù)截取【F2】單元格左邊四個(gè)字符）

第二個(gè)條件對(duì)是：「 $B$3:$B$8,"手續(xù)費(fèi)" 」。

在單元格【$B$3:$B$8】中，查找手續(xù)費(fèi)的內(nèi)容。

如果同時(shí)符合條件的話，在【$D$3:$D$8】單元格區(qū)域中對(duì)應(yīng)的行金額進(jìn)行求和。

這里的第二個(gè)條件對(duì)和【$D$3:$D$8】這個(gè)求和區(qū)域正好與第一個(gè)條件對(duì)錯(cuò)了一行。

▋方法二：SUM 數(shù)組公式

這里我們可以將兩個(gè)條件利用文本連接符錯(cuò)位連接的方法變成一個(gè)條件，然后再與求和區(qū)域相乘的方法來(lái)解決。

公式如下：

• =SUM(($B$2:$B$7&$B$3:$B$8=F2)*$D$3:$D$8)
  

公式解析：

這里的思路跟上面的 SUMIFS 多條件求和的思路是一樣的。

為了能讓大家看明白，我們這里加兩個(gè)輔助列演示下，如下圖：

圖中我們將【B3：B7】單元格復(fù)制到【C2：C6】，

將【E3：E7】單元格復(fù)制到【F2：F6】。

這樣排列的話，跟常規(guī)的求和套路完全一樣了。

• $B$2:$B$7&$B$3:$B$8
  

結(jié)果是：

{"常規(guī)培訓(xùn)手續(xù)費(fèi)";"手續(xù)費(fèi)常規(guī)培訓(xùn)";"常規(guī)培訓(xùn)手續(xù)費(fèi)";"手續(xù)費(fèi)場(chǎng)地租賃";"場(chǎng)地租賃手續(xù)費(fèi)";"手續(xù)費(fèi)"}

與輔助列圖中的【B2：B7】與【C2：C7】連接的結(jié)果完全一樣。

再與【F2】單元格中的內(nèi)容（常規(guī)培訓(xùn)手續(xù)費(fèi)）進(jìn)行比較，如果相等就返回 TRUE，否則返回 FALSE。

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

最后與$D$3:$D$8 單元格中的金額相乘。

TRUE 相當(dāng)于 1，F(xiàn)ALSE 相當(dāng)于 0。

結(jié)果如下：

{-10;0;-10;0;0;0}

最后用 SUM 求出結(jié)果。即：-20

知識(shí)擴(kuò)展

這里我們?cè)俜窒硪环N解題思路，就是透視表法。

從上面的原始記錄來(lái)看，是屬于一維數(shù)據(jù)表格，只要稍加變化就可以使用強(qiáng)大的透視表來(lái)處理了。

如下圖：

在【E】列增加一個(gè)輔助列，并在【E2】單元格輸入如下公式：

• =IF(B2="手續(xù)費(fèi)",E1,B2)
  

公式解析：

如果【B2】單元格等于手續(xù)費(fèi)，就返回【E1】單元格的內(nèi)容，否則返回【B2】單元格本身。

公式的意思很簡(jiǎn)單。主要目的就是讓手續(xù)費(fèi)歸屬于它上面一個(gè)單元格的項(xiàng)目?jī)?nèi)容。

然后，我們選中這個(gè)區(qū)域作為數(shù)據(jù)源，然后依次點(diǎn)擊【插入】-【數(shù)據(jù)透視表】-調(diào)出透視表對(duì)話框，直接點(diǎn)【確定】。

在新生成的工作表中，將【輔助列】和【項(xiàng)目】拖到行區(qū)域，將【金額】拖到值區(qū)域。如下圖：

這樣大體上的統(tǒng)計(jì)就完成了。

進(jìn)行如下設(shè)置，點(diǎn)擊【設(shè)計(jì)】-【報(bào)表布局】-【以表格形式顯示】。

最后篩選項(xiàng)目中的手續(xù)費(fèi)即可。

今天，我們分享了一個(gè)很特別的求和方法：錯(cuò)行求和。

? 利用 SUMIFS 多條件錯(cuò)行求和。

? 利用 SUM 數(shù)組公式錯(cuò)行求和。

? 利用輔助列+透視表的方法進(jìn)行錯(cuò)行求和。

當(dāng)然在實(shí)際工作中，還會(huì)遇到錯(cuò)列求和、隔行求和、隔列求和等等。

大家可以參考今天的文章思路來(lái)解決。

好了，如果大家還有哪些搞不定的 Excel 問(wèn)題，

可以報(bào)名參加我們秋葉《3 天 Excel 集訓(xùn)營(yíng)》。

這里不僅有老師、助教為你1V1?群內(nèi)答疑解惑！

還能學(xué)習(xí)更多有用的 Excel 小技巧！

僅需 1 元！

現(xiàn)在就掃碼購(gòu)課加班班微信，

助你成為效率達(dá)人！

??????

關(guān)注我【秋葉 Excel】

還能免費(fèi)領(lǐng)

40+實(shí)用圖表和300+函數(shù)公式學(xué)習(xí)

還有不定期小禮物小福利，歡迎持續(xù)關(guān)注呀~

??

*廣告