Excel函數(shù)學習42：SUMIFS函數(shù)

L羅樂 2018-03-23

展開全文

SUMIFS函數(shù)對一組給定條件指定的單元格求和。

圖1

什么情況下使用SUMIFS函數(shù)?

SUMIFS函數(shù)用于計算某區(qū)域中滿足一個或多個條件的單元格的總和。它能夠：

多條件求和
配合VLOOKUP函數(shù)查找多個列中的值并獲取相對應的值
按每行匯總

SUMIFS函數(shù)語法

SUMIFS函數(shù)有多個參數(shù)，其語法如下：

SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)

sum_range: 必需，指定求和的單元格區(qū)域。
criteria_range1: 必需，參數(shù)criteria1測試的單元格區(qū)域。
criteria1：必需，指定對參數(shù)criteria_range1中的哪些單元格求和的條件。
在criteria_range1中搜索滿足criteria1的項，一旦找到，就會計算sum_range中相應值的和。
criteria_range2,criteria2,…. 可選參數(shù)，作用同criteria_range1和criteria1。最多可以使用127個區(qū)域/條件對。
求和時會忽略掉參數(shù)sum_range中的空或文本值。
criteria可以是數(shù)字、表達式、單元格引用、文本或公式，可以使用通配符，不能多于255個字符。如果有文本、數(shù)學符號、邏輯符號（例如=、、-、/、*），則應添加雙引號。

SUMIFS函數(shù)陷阱

SUMIFS函數(shù)是Excel 2007中新增的函數(shù)，因此適用于Excel 2007及以上版本。

如果要測試文本字符串值，那么應對條件參數(shù)criteria1等的值使用引號，否則不會顯示預期的結(jié)果，而是顯示0。sum_range中的值包含TRUE和FALSE時，由于邏輯值的求值方式不同，可能會在將其相加時導致意外結(jié)果。

參數(shù)criteria_range與參數(shù)sum_range指定的單元格區(qū)域的行列數(shù)必須相同。

注意，SUMIFS函數(shù)和SUMIF函數(shù)的參數(shù)順序。參數(shù)sum_range在SUMIFS函數(shù)中是第1個參數(shù)，而在SUMIF函數(shù)中是第3個參數(shù)。

示例1: 多條件求和

如下圖2所示的產(chǎn)品銷售數(shù)據(jù)表。

圖2

計算東區(qū)吳小花銷售的產(chǎn)品數(shù)量，公式為：

=SUMIFS(D2:D12,B2:B12,'=東區(qū)',C2:C12,'吳小花')

公式在單元格區(qū)域B2:B12中查找“東區(qū)”，在C2:C12中查找“吳小花”，然后計算D2:D12中同時滿足這兩個條件的單元格中數(shù)值的總和，結(jié)果為154。

計算東區(qū)除香蕉以外的產(chǎn)品銷售數(shù)量，公式為：

=SUMIFS(D2:D12,A2:A12,'<>香蕉',B2:B12,'東區(qū)')

公式在Criteria1中使用<>排除香蕉，在B2:B12中查找東區(qū)，然后計算D2:D12中同時滿足這兩個條件的單元格中數(shù)值之和，結(jié)果為135。

計算由吳小花和孫大壯銷售的以香開頭的產(chǎn)品的數(shù)量，公式為：

=SUMIFS(D2:D12,A2:A12,'=香*',C2:C12,'吳小花') SUMIFS(D2:D12,A2:A12,'=香*',C2:C12,'孫大壯')

公式中使用了通配符*，作為條件查找香開頭的產(chǎn)品名。因為條件既包括了AND條件，又包括了OR條件，所以使用了兩個SUMIFS函數(shù)來實現(xiàn)目的。

示例表明，可以在SUMIFS函數(shù)的查找條件中使用通配符（?或*），來查找近似項。其中，問號匹配任何單個字符，星號匹配任意數(shù)量的字符。如果要查找問號或星號本身，在符號前面加上~號。

計算香梨、香蕉和蘋果的銷售數(shù)量之和，公式為：

=SUM(SUMIFS($D$2:$D$12,$A$2:$A$12,{'香梨','香蕉','蘋果'}))

在公式中，我們使用了花括號將要求銷售數(shù)量的產(chǎn)品列出來，并使用SUM函數(shù)來求和。如果不加上SUM函數(shù)，我們將只會得到香梨在表中第1次出現(xiàn)的數(shù)量。

示例2：配合VLOOKUP函數(shù)查找多個列中的值并獲取相對應的值

示例數(shù)據(jù)如圖3所示。

圖3

現(xiàn)在，通過城市和行政區(qū)獲取所在的省份，如圖4所示。

圖4

在單元格D12中輸入公式：

=VLOOKUP(SUMIFS($A$2:$A$8,$B$2:$B$8,B12,$C$2:$C$8,C12),$A$2:$E$8,5,0)

下拉至D14，結(jié)果如圖5所示。

圖5

注意，使用SUMIFS 函數(shù)必須保證列A中的編號沒有重復值且是數(shù)值，這樣才能實現(xiàn)正確的查找。當然，這個技巧也可以用于到日期。

示例3：按每行匯總

示例數(shù)據(jù)如圖6所示，要求每天根據(jù)區(qū)域和物品進行求和匯總。例如，2018年3月8日西區(qū)鋼筆的數(shù)量為78，但從2018年3月1日起至3月8日西區(qū)鋼筆的總數(shù)量為247。如果列C為空，則匯總到該日期為止所在區(qū)域的總數(shù)量，如2018年3月4日東區(qū)物品總數(shù)量為339。