史上最全Excel條件求和函數(shù)SUMIF經(jīng)典教程

史上最全Excel條件求和函數(shù)SUMIF經(jīng)典教程

在職場(chǎng)辦公中，經(jīng)常需要對(duì)數(shù)據(jù)進(jìn)行條件求和匯總，SUMIF函數(shù)是工作中使用頻率超高的條件求和函數(shù)之一。

本文介紹了SUMIF函數(shù)的豐富用法，便于你在自己的實(shí)際工作中直接借鑒和使用。

函數(shù)基礎(chǔ)語(yǔ)法解析

SUMIF函數(shù)可以對(duì)范圍中符合指定條件的值求和，該函數(shù)擁有十分強(qiáng)大的條件求和功能，在工作中有極其廣泛的應(yīng)用，其基本語(yǔ)法為：

SUMIF(range,criteria,[sum_range])

range：必需。用于條件計(jì)算的單元格區(qū)域。每個(gè)區(qū)域中的單元格都必須是數(shù)字或名稱、數(shù)組或包含數(shù)字的引用。空值和文本值將被忽略。

criteria：必需。用于確定對(duì)哪些單元格求和的條件，其形式可以為數(shù)字、表達(dá)式、單元格引用、文本或函數(shù)。

sum_range：可選。要求和的實(shí)際單元格(如果要對(duì)未在range參數(shù)中指定的單元格求和)。如果省略sum_range參數(shù)，Excel會(huì)對(duì)在range參數(shù)中指定的單元格(即應(yīng)用條件的單元格)求和。

說明：

(1)criteria中的任何文本條件或任何含有邏輯或數(shù)學(xué)符號(hào)的條件都必須使用雙引號(hào)括起來。如果條件為數(shù)字，則無需使用雙引號(hào)。

(2)criteria參數(shù)中支持使用通配符(包括問號(hào)“?”和星號(hào)“*”)。問號(hào)匹配任意單個(gè)字符;星號(hào)匹配任意一串字符。如果要查找實(shí)際的問號(hào)或星號(hào)，請(qǐng)?jiān)谠撟址版I入波形符“~”。

(3)使用SUMIF函數(shù)匹配超過255個(gè)字符的字符串或字符串#VALUE!時(shí)，將返回不正確的結(jié)果。

(4)當(dāng)sum_range參數(shù)與range參數(shù)的大小和形狀可以不同。求和的實(shí)際單元格通過以下方法確定：使用sum_range參數(shù)中左上角的單元格作為起始單元格，然后包括與range參數(shù)大小和形狀相對(duì)應(yīng)的單元格。注意，這種情況下會(huì)使SUMIF函數(shù)具有易失性，即引發(fā)工作表重算。

SUMIF函數(shù)本身不是易失性函數(shù)，但當(dāng)SUMIF函數(shù)中的range和sum_range參數(shù)包含的單元格個(gè)數(shù)不相等時(shí)，會(huì)具備易失性。如以下公式：

=SUMIF(B2:B9,”女”,C2:C3)

=SUMIF(B2:B9,”女”,C2:C99)

=SUMIF(B2:B9,”女”,C2)

三個(gè)公式返回的結(jié)果一致，SUMIF函數(shù)的sum_range參數(shù)的單元格個(gè)數(shù)都與range的單元格個(gè)數(shù)不同，但都會(huì)將sum_range的區(qū)域按照C2:C9計(jì)算，即以C2為起始單元格，延伸至大小和形狀與B2:B9相同的單元格。相當(dāng)于以下公式：

=SUMIF(B2:B9,”女”,C2:C9)

易失性會(huì)引發(fā)工作表的重新計(jì)算，計(jì)算時(shí)間會(huì)比預(yù)期的要長(zhǎng)，工作中應(yīng)盡量避免這種情況出現(xiàn)。

(5)SUMIF函數(shù)中criteria參數(shù)的格式會(huì)限定其選擇條件求和的范圍。即如果第二參數(shù)是數(shù)值，SUMIF函數(shù)就只對(duì)第一參數(shù)是數(shù)值格式的單元格對(duì)應(yīng)的求和區(qū)域中進(jìn)行統(tǒng)計(jì)，而忽略其他格式如文本、邏輯值、錯(cuò)誤值等。利用SUMIF函數(shù)的這個(gè)特性，我們可以排除錯(cuò)誤值進(jìn)行求和。

單字段單條件求和

工作中最常見的需求當(dāng)然就是單條件求和啦，SUMIF函數(shù)在這方面可謂得心應(yīng)手!

下面就來講講SUMIF函數(shù)統(tǒng)計(jì)單字段單條件求和條件求和，還講解了當(dāng)SUMIF的第三參數(shù)缺省時(shí)的運(yùn)算方式和原理。

SUMIF函數(shù)的單條件求和應(yīng)用非常廣泛，在很多情況下，當(dāng)條件區(qū)域和求和區(qū)域重合時(shí)還可以簡(jiǎn)化公式寫法，下面結(jié)合一個(gè)案例來介紹具體的方法。

要求從數(shù)據(jù)源中統(tǒng)計(jì)90分以上的成績(jī)之和，先給出公式

=SUMIF(B2:B12,”>90″)

單字段多條件求和

上一節(jié)教程中我們學(xué)會(huì)了SUMIF函數(shù)的單條件求和，那么當(dāng)工作中出現(xiàn)對(duì)某個(gè)字段并列多條件求和的需求，又如何應(yīng)對(duì)呢?

我們結(jié)合下面這個(gè)案例來具體介紹。

表格中左側(cè)是數(shù)據(jù)源區(qū)域，要求統(tǒng)計(jì)北京分公司、上海分公司、廣州分公司這三家銷售額總和，如果是只求一家分公司(如北京)的銷售額那很簡(jiǎn)單，公式為

=SUMIF(A2:A12,”北京”,B2:B12)

多家怎么辦呢?最直接的辦法當(dāng)然是這樣：

=SUMIF(A2:A12,”北京”,B2:B12)+ SUMIF(A2:A12,”上海”,B2:B12)+ SUMIF(A2:A12,”廣州”,B2:B12)

是不是只有這種方法呢?如果需要統(tǒng)計(jì)的分公司增加，豈不是公式越來越長(zhǎng)?

當(dāng)然會(huì)有更好的辦法啦!

給出這里使用的公式：

=SUM(SUMIF(A2:A12,{“北京”,”上海”,”廣州”},B2:B12))

統(tǒng)計(jì)前三名成績(jī)和

前面的課程中我們學(xué)會(huì)了SUMIF函數(shù)的單條件求和、多條件求和，那么當(dāng)我們?cè)诠ぷ髦杏龅缴婕皵?shù)值大小的問題，該用什么思路去解決呢?

下面這個(gè)案例，我們就用SUMIF函數(shù)結(jié)合LARGE函數(shù)配合搞定一個(gè)極值統(tǒng)計(jì)問題。

表格中左側(cè)是數(shù)據(jù)源區(qū)域，要求統(tǒng)計(jì)前三名成績(jī)之和。

我們可以分為兩步來思考這個(gè)問題，第一步是需要從數(shù)據(jù)中用公式提取前三名的成績(jī)，第二部是將它們匯總求和。這樣即可數(shù)據(jù)源變動(dòng)，前三名成績(jī)也會(huì)隨公式結(jié)果動(dòng)態(tài)更新，從而始終保證結(jié)果的正確。

這里給出公式

=SUMIF(B2:B12,”>”&LARGE(B2:B12,4))

模糊條件求和

有時(shí)我們要按照模糊條件求和，而SUMIF函數(shù)支持通配符的使用，下面我們結(jié)合案例來介紹模糊條件求和的方法。

表格左側(cè)是數(shù)據(jù)源區(qū)域，要求統(tǒng)計(jì)姓“張”的員工成績(jī)之和，也就是姓名中以“張”開頭的，給出公式。

=SUMIF(A2:A12,”張*”,B2:B12)

根據(jù)日期區(qū)間統(tǒng)計(jì)

在工作中我們經(jīng)常遇到按日期區(qū)間統(tǒng)計(jì)的需求，比如需要統(tǒng)計(jì)月初到當(dāng)前日期的銷售額，或統(tǒng)計(jì)周年慶(比如歷時(shí)5天)的銷售額……針對(duì)這類條件求和如何實(shí)現(xiàn)呢?

今天我們結(jié)合一個(gè)簡(jiǎn)單案例，來介紹以日期區(qū)間作為條件的求和方法。

下面給出公式。

=SUM(SUMIF(A2:A12,{“>=2016/4/1″,”>2016/4/5″},B2:B12)*{1,-1})

統(tǒng)計(jì)登記人非空的入庫(kù)數(shù)

工作中的數(shù)據(jù)源可能來自多種渠道，有的是系統(tǒng)導(dǎo)出，有的是人工填寫收集，有的是第三方機(jī)構(gòu)提供，都難免遇到數(shù)據(jù)源中某字段有空值或者無效值的情況，這時(shí)往往需要排除這些無效記錄進(jìn)行統(tǒng)計(jì)。

今天我們結(jié)合一個(gè)簡(jiǎn)單案例，講解如何利用SUMIF函數(shù)統(tǒng)計(jì)求和條件涉及非空值的方法。

登記人為空的記錄都屬于無效記錄，統(tǒng)計(jì)入庫(kù)數(shù)量時(shí)不予考慮，僅統(tǒng)計(jì)登記人非空的入庫(kù)數(shù)。

給出公式：

=SUMIF(A2:A8,”*”,B2:B8)

隔列分類匯總

SUMIF函數(shù)強(qiáng)大的條件求和功能在多種工作場(chǎng)景中均有廣泛應(yīng)用。無論是財(cái)務(wù)還是市場(chǎng)銷售人員，都會(huì)面臨在數(shù)據(jù)源中跨列條件求和的需求，比如在包含計(jì)劃和實(shí)際銷售額的表格中分別匯總計(jì)劃總和、實(shí)際完成總和。

在這里案例中，每個(gè)業(yè)務(wù)員制定的計(jì)劃數(shù)據(jù)和實(shí)際完成數(shù)據(jù)交替出現(xiàn)，最后要在黃色區(qū)域輸入公式，完成對(duì)應(yīng)的計(jì)劃和實(shí)際總和的統(tǒng)計(jì)。

在H3單元格輸入以下公式，填充至H3:I9單元格區(qū)域即可

=SUMIF($B$2:$G$2,H$2,$B3:$G3)

實(shí)現(xiàn)查找引用功能

看了這個(gè)標(biāo)題有的小伙們就納悶了，查找引用不是VLOOKUP函數(shù)、INDEX+MATCH他們的事嗎?怎么SUMIF也來湊熱鬧?

你沒看錯(cuò)，SUMIF除了條件求和，在一些場(chǎng)景下也能實(shí)現(xiàn)查找引用功能。

這個(gè)案例的表格中，左側(cè)是數(shù)據(jù)源區(qū)域，右側(cè)的黃色區(qū)域是公式區(qū)域。

要實(shí)現(xiàn)按照業(yè)務(wù)員查找對(duì)應(yīng)的成績(jī)，一個(gè)公式搞定。

給出公式(H2輸入)

=SUMIF($A$2:$A$12,$G2,B$2:B$12)

排除錯(cuò)誤值求和

出于各種原因，我們?cè)谔幚頂?shù)據(jù)時(shí)難免遇到錯(cuò)誤值，當(dāng)數(shù)據(jù)源中包含錯(cuò)誤值時(shí)，普通的求和公式返回的也是錯(cuò)誤值，那么如何既能排除錯(cuò)誤值又能不影響條件求和呢?

我們來看這個(gè)案例：

如果是職場(chǎng)小白，一看這數(shù)據(jù)源就蒙圈了，各種錯(cuò)誤值差不多都來報(bào)道啦，怎么辦?

行家伸伸手，便知有沒有

給出公式，D2單元格輸入以下公式

=SUMIF(B2:B12,”<9e307″)

統(tǒng)計(jì)入庫(kù)日期非空的數(shù)量和

我們遇到的數(shù)據(jù)源難免遇到某字段有空值或者無效值的情況，這時(shí)往往需要排除這些無效記錄進(jìn)行統(tǒng)計(jì)。

之前我們介紹過當(dāng)文本數(shù)據(jù)中摻雜空值的處理方法，見《SUMIF函數(shù)統(tǒng)計(jì)登記人非空的入庫(kù)數(shù)》，今天再來介紹下日期數(shù)據(jù)中摻雜空值的處理方法。

下面我們結(jié)合案例，講解如何利用SUMIF函數(shù)統(tǒng)計(jì)求和條件涉及空值的方法。

入庫(kù)日期為空的記錄都屬于無效記錄，統(tǒng)計(jì)入庫(kù)數(shù)量時(shí)不予考慮，僅統(tǒng)計(jì)登記人非空的入庫(kù)數(shù)。

給出公式：

=SUMIF(A2:A8,”<>”,B2:B8)

多列區(qū)域條件求和

前面的教程中我們介紹了SUMIF函數(shù)各種各樣的條件求和方法，都是條件區(qū)域只有一列，求和區(qū)域也只有一列的場(chǎng)景，那么如果遇到條件區(qū)域和求和區(qū)域都是多列區(qū)域，如何處理呢?

上圖展示的是某企業(yè)的員工工號(hào)信息表，工號(hào)和對(duì)應(yīng)姓名放置在多列區(qū)域中，需要在B10:B12單元格區(qū)域根據(jù)員工的姓名提取對(duì)應(yīng)的員工工號(hào)。

給出公式

在B10單元格輸入以下公式，將公式向下復(fù)制到B12單元格。

=SUMIF(B$2:D$6,A10,A$2:C$6)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。