在OFFICE官方函數(shù)列表中，常用的求和函數(shù)有三個(gè)：SUM,SUMIF,SUMIFS。

他們每一個(gè)都各自專長(zhǎng)：

SUM——單行、單列、多行、多列，無(wú)條件求和;

SUMIF——行、列，一個(gè)條件求和;

SUMIFS——行、列，多條件求和。

根據(jù)上表可知，常用的三個(gè)求和函數(shù)，均無(wú)法滿足所有的求和要求。

是否有一個(gè)函數(shù)，可以滿足所有的求和要求？

目前OFFICE最新版本為OFFICE 2019，盡管有新增函數(shù)，但并沒(méi)有包含與求和有關(guān)的項(xiàng)目。

那么有沒(méi)有函數(shù)組合，滿足多條件、多行或多列求和呢？

曾經(jīng)在《如何快速解決多條件匯總難題——Excel中的三個(gè)簡(jiǎn)單方法》中，提到過(guò)數(shù)學(xué)集合的概念，并將其應(yīng)用于多條件求和。

既然集合可用于多條件，那么也將其用于多行或多列求和。

實(shí)際上多行或多列，在Excel中稱之為"區(qū)域"。因此只要能夠使函數(shù)組合識(shí)別到數(shù)據(jù)"區(qū)域"，就可以進(jìn)行計(jì)算。

與區(qū)域計(jì)算相關(guān)的函數(shù)常用的有兩個(gè)，一個(gè)是SUMPRODUCT，一個(gè)是OFFSET。

下面就以統(tǒng)計(jì)最近一屆奧運(yùn)獎(jiǎng)牌榜中國(guó)的金牌及銀牌的總數(shù)為案例分別說(shuō)明兩個(gè)方法。

方法一：SUMPRODUCT數(shù)組計(jì)算實(shí)現(xiàn)區(qū)域多條件求和

觀察SUMPRODUCT的參數(shù)是數(shù)據(jù)區(qū)域1、2……255。因此，可直接進(jìn)行多區(qū)域計(jì)算，而計(jì)算條件，可作為區(qū)域參數(shù)即可，兩者用符號(hào)"*"連接。

根據(jù)獎(jiǎng)牌榜統(tǒng)計(jì)要求，包含一個(gè)求和區(qū)域及兩個(gè)條件：

求和區(qū)域?yàn)?金牌"E、"銀牌"F兩列；

條件1是"最近一屆"，也就是在年度A列選擇最大的年份，這里使用MAX函數(shù)即可找到；

條件2是國(guó)家為"中國(guó)"。

將以上條件分別填入函數(shù)SUMPRODUCT中，公式為：

SUMPRODUCT((E2:F10)*(A2:A10=MAX(A2:A10))*(D2:D10="中國(guó)"))=44

表哥TIPS:

SUMPRODUCT看似很好用，但由于參數(shù)個(gè)數(shù)的限制，最多不能超過(guò)255個(gè)，使用時(shí)需要注意。

方法二： OFFSET確定求和區(qū)域，加上SUMIFS多條件的特點(diǎn)。

根據(jù)Excel官方說(shuō)明中，OFFSET既可以返回一個(gè)單元格，也可以返回一片數(shù)據(jù)區(qū)域。其中前三個(gè)參數(shù)是必需項(xiàng)目。

表哥Tips：

若第2、3兩個(gè)參數(shù)為0，則可為空，但逗號(hào)不可省略。

同樣以統(tǒng)計(jì)最近一屆咱國(guó)家收獲金牌和銀牌數(shù)為例。這次我們使用

OFFSET來(lái)定義區(qū)域。

由于所選區(qū)域?yàn)镋、F兩行，所以要用到數(shù)學(xué)中集合的概念。公式為：

OFFSET(E2:E10,,{0,1})

公式中第三個(gè)參數(shù)的含義是，向右移動(dòng)的列數(shù)。OFFSET中引入了集合{0,1}，則可理解為此公式包含兩個(gè)區(qū)域：OFFSET(E2:E10,,0)和OFFSET(E2:E10,,0)。

確定了求和數(shù)據(jù)區(qū)域后，

結(jié)合SUMIFS函數(shù)的多條件的特點(diǎn)，再公式最外層加上SUM以進(jìn)行集合運(yùn)算。組合公式為：

SUM(SUMIFS(OFFSET(E2:E10,,{0,1}),A2:A10,MAX(A2:A10),D2:D10,"中國(guó)"))

表哥Tips：

(1) 注意確認(rèn)OFFSET返回區(qū)域是否在系統(tǒng)界內(nèi)，超出系統(tǒng)極限值，會(huì)報(bào)錯(cuò)；

(2) SUMIFS的最多條件數(shù)是127，注意設(shè)置條件數(shù)在此范圍內(nèi)。

以上就是多行多列多條件求和的兩種常用方法。

你學(xué)會(huì)了嗎？