求和、查找、計(jì)數(shù)這三類函數(shù)，可謂是最為常用的 Excel 函數(shù)。

而在求和統(tǒng)計(jì)中，以?SUM、SUMPRODUCT、SUMIF 單條件求和或者 SUMIFS 多條件求和使用的最多。

雖然，SUM、SUMPRODUCT 也可以使用數(shù)組的方式實(shí)現(xiàn)條件求和，但是對(duì)于初學(xué)者來(lái)說(shuō)，內(nèi)存數(shù)組學(xué)習(xí)起來(lái)有一定難度，而且在數(shù)據(jù)相對(duì)較多的時(shí)候，使用內(nèi)存數(shù)組會(huì)使表格非?？D。

所以對(duì)于條件求和，建議優(yōu)先使用 SUMIF 或者 SUMIFS。

SUMIF 的語(yǔ)法結(jié)構(gòu)是：
SUMIF (條件區(qū)域，條件，求和區(qū)域)

SUMIFS 的語(yǔ)法結(jié)構(gòu)是：
SUMIFS(求和區(qū)域，條件區(qū)域 1，條件 1, 條件區(qū)域 2，條件 2，……)

此處的條件對(duì)，最多可以寫(xiě) 127 對(duì)。

有的小伙伴們?cè)谑褂盟麄兊臅r(shí)候，經(jīng)常會(huì)遇到一些無(wú)法求和或者求和出錯(cuò)的情況。

下面我們一起來(lái)看看這兩個(gè)函數(shù)到底有哪些坑！

情形一：長(zhǎng)數(shù)字

如下圖，是一個(gè)網(wǎng)購(gòu)訂單明細(xì)表。

要求是：根據(jù)日期和訂單號(hào)統(tǒng)計(jì)出總金額。

這個(gè)問(wèn)題，看似還是比較簡(jiǎn)單的吧。通常我們可以寫(xiě)出如下公式：

公式如下：

=SUMIFS(C:C,A:A,E2,B:B,F2)

公式解析：

在【A】列中查找【E2】單元格的日期，并且在【B】列中查找【F2】訂單號(hào)，如果同時(shí)符合這兩個(gè)條件的話，對(duì)【C】列的金額進(jìn)行求和。

仔細(xì)觀察下，【G2】返回的結(jié)果是錯(cuò)的！它將【C2:C4】單元格區(qū)域的數(shù)量一并加起來(lái)了。

這是為什么呢？

究其原因是：這類以純數(shù)字作為訂單號(hào)，而且數(shù)字的長(zhǎng)度超過(guò)了 15 位的數(shù)據(jù)，該函數(shù)會(huì)將超過(guò) 15 位數(shù)字之后的數(shù)字全部視為 0。

以上案例中，訂單號(hào)一共由 19 位數(shù)字組成。也就是說(shuō)：在統(tǒng)計(jì)的時(shí)候，只要訂單號(hào)前 15 位數(shù)字完全相同，都會(huì)將其數(shù)量統(tǒng)計(jì)在內(nèi)。即將【C2:C4】的金額都統(tǒng)計(jì)，如下圖：

解決方法：可以在訂單號(hào)之后連接一個(gè)通配符（星號(hào)*）即可。

=SUMIFS(C:C,A:A,E2,B:B,F2&"*")

這樣連接一個(gè)星號(hào)（*），統(tǒng)計(jì)結(jié)果就正確無(wú)誤了。

其原理是：純數(shù)字連接一個(gè)通配符之后，將原來(lái)的數(shù)字強(qiáng)制變成文本，最終以文本的方式來(lái)進(jìn)行統(tǒng)計(jì)。而文本沒(méi)有數(shù)字位數(shù)的限制。

PS：如果訂單號(hào)位數(shù)不一樣，連接通配符（星號(hào)*）時(shí)要特別注意下，有時(shí)會(huì)產(chǎn)生統(tǒng)計(jì)錯(cuò)誤。比如下面這樣：

公式同上。

要統(tǒng)計(jì)的數(shù)字編號(hào)有 15 位以內(nèi)和 15 位以上的訂單號(hào)，連接通配符之后，統(tǒng)計(jì)結(jié)果出錯(cuò)了。

原因是【F2】雖然與【B3】?jī)?nèi)容完全一樣，但是連接通配符之后，會(huì)認(rèn)為【F2】和【B2】【B4】的訂單號(hào)也一樣了，大家在實(shí)際應(yīng)用的時(shí)候需要注意下。

情形二：通配符

下面是一個(gè)產(chǎn)品規(guī)格明細(xì)表：

需要求出產(chǎn)品的總數(shù)量。

公式的基本思路還是跟上面一樣。

公式如下：

=SUMIFS(C:C,A:A,E2,B:B,F2)

公式解析：

在【A】列中查找【E2】單元格的品名，并且在【B】列中查找【F2】中的規(guī)格，如果同時(shí)符合這兩個(gè)條件的話，對(duì)【C】列的數(shù)據(jù)進(jìn)行求和。

統(tǒng)計(jì)的結(jié)果是將第 2 行、第 5 行和第 7 行的數(shù)量全部相加了！

這又是為什么？

其實(shí)原因就在這個(gè)通配符本身。

這里查找內(nèi)容是：10*30

中間的星號(hào)可以代表任意多個(gè)字符。比如可以是：100*30 或者 101*30 或者 10*130 等等。只要開(kāi)始是 10，結(jié)尾是 30 的規(guī)格，都將統(tǒng)計(jì)在內(nèi)。

在這種情況下，我們想要得到正確的結(jié)果，需要去除通配符的特性。把公式改成如下形式：

=SUMIFS(C:C,A:A,E2,B:B,SUBSTITUTE(F2,"*","~*"))

使用 SUBSTITUTE 替換函數(shù)將規(guī)格中的通配符星號(hào)前面加一個(gè)波形符（~），就可以了。

該波形符的作用是將通配符變成普通字符來(lái)處理。

substitute 語(yǔ)法結(jié)構(gòu)：

substitute(包含需要替換的文本,需要替換的舊文本,需要替換的新文本)

情形三：空白單元格

如下圖，還是一個(gè)產(chǎn)品規(guī)格明細(xì)表。

由于其中的電機(jī)沒(méi)有寫(xiě)規(guī)格，所以就以空單元格顯示。

公式如下：

=SUMIFS(C:C,A:A,E2,B:B,F2)

公式解析：

在【A】列中查找【E2】單元格的品名，并且在【B】列中查找【F2】的規(guī)格，如果同時(shí)符合這兩個(gè)條件的話，對(duì)【C】列的數(shù)量進(jìn)行求和。

結(jié)果電機(jī)的數(shù)量為 0！

又出錯(cuò)了？

這這這又是什么坑？

這個(gè)例子中又沒(méi)有長(zhǎng)數(shù)字，也沒(méi)有通配符，這回到底是咋回事呢？

找了半天，問(wèn)題終于找到了，問(wèn)題就出在空白單元格這里。

該函數(shù)對(duì)于空白單元格有特別的處理方法。需要在 F2 單元格之后連接一對(duì)英文半角的雙引號(hào)。

公式更改如下：

=SUMIFS(C:C,A:A,E2,B:B,F2&"")

在 F2 后面連接一對(duì)英文半角的雙引號(hào)，以代表空白單元格這個(gè)條件即可。

此時(shí)統(tǒng)計(jì)結(jié)果正確無(wú)誤。

寫(xiě)在最后

今天我們分享了 SUMIFS 這個(gè)函數(shù)的三個(gè)坑爹的情形。

? 處理長(zhǎng)數(shù)字的編號(hào)，如訂單號(hào)，銀行卡號(hào)，身份證號(hào)。

? 處理帶有通配符的數(shù)據(jù)。

? 處理帶有空白單元格的數(shù)據(jù)。

對(duì)于如下列舉的函數(shù)也有同樣的問(wèn)題，小伙伴們?cè)谑褂脮r(shí)，要特別留意下哦！

如果你想系統(tǒng)性學(xué)習(xí) Excel，掌握更多 Excel 技能。

正好，我們《秋葉 Excel 3 天集訓(xùn)營(yíng)》專為職場(chǎng)人準(zhǔn)備，全部基于職場(chǎng)真實(shí)表格案例設(shè)計(jì)，還有很多超實(shí)用 Excel 技巧教學(xué)。

每天學(xué)習(xí)大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實(shí)進(jìn)階每一步。

秋葉 Excel 3 天集訓(xùn)營(yíng)

原價(jià)?99 元??

現(xiàn)在?只需 1?元?

每天學(xué)習(xí)?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報(bào)名吧！

??????

好了，今天的分享就是這些，如果喜歡我的文章，請(qǐng)點(diǎn)贊&轉(zhuǎn)發(fā)支持一下吧！我們下次再見(jiàn)！