SUMIF，你对TA了解有多少？

sumif

深入理解SUMIF：如何多表多列多条件求和？

一、基本用法

翻看Excel帮助，SUMIF作用是“根据指定条件对若干单元格求和”，言简意不赅，惜字如金啊。说明白点就是“对条件区域进行判断，如果某些单元格满足指定条件，则对求和区域所对应的若干单元格进行求和”。

如图一，求A列姓名为龙逸凡的金额之和，则公式为“=SUMIF(A2:A11,"龙逸凡",B2：B11)”。结果为17。

技巧1：

SUMIF可以使用通配符，如统计龙姓员工的金额之和，则公式为=SUMIF(A2:A11,"龙*",B2:B11)。

====================

技巧2：

如果将SUMIF的第三个参数省略，则对条件区域中的单元格求和，如公式=SUMIF(B2:B11,">3")统计B2：B11单元格区域大于3的数之和。

注意：不能简写成=SUMIF(B2:B10,">3",)。

====================

技巧3：

SUMIF不但能对列区域求和，还能对行区域求和，大家不要被自己的固有思维限制了。如公式“=SUMIF(A1:H1,"龙逸凡",A2:H2)”。

上面的基本形式大家都会，下面我们逐步深入。

二、简化形式

一般情况，SUMIF的第一参数和第三参数的区域应该是单列，并且大小相同，但是，如果我们两参数区域不等，会怎么样呢？比如，将本文第一个公式写成下面的公式会怎么样呢？

=SUMIF(A2:A11,"龙逸凡",B2)

=SUMIF(A2:A11,"龙逸凡",B2:B3)

=SUMIF(A2:A11,"龙逸凡",B2:B10)

=SUMIF(A2:A11,"龙逸凡",B2:C1000)

=SUMIF(A2:A11,"龙逸凡",B2:B4:D1000)

经测试，上面的五个公式等价，结果是一样的，都是17。也许你已经看出规律，第三参数真正起作用的就是第三参数单元格区域的左上角那个单元格。因而我们完全可以将公式简化成=SUMIF(A2:A5,">160000",B2)。

题外话：

B2:B4:D1000这种奇怪的形式实际上就是此单元格区域最左最右最上最下单元格所组成的矩形区域，比如=SUM(B2:B3:B5:D5:D9:D14)，双击单元格编辑公式时Excel显示的引用范围为B2:B3、B5:D5、D9:D14，但实际上就是对B2:D14组成的矩形区域求和，而不是等同于=SUM(B2:B3，B5:D5，D9:D14)。

三、定位原理

既然起作用的就是第三参数单元格区域左上角的单元格，那其真正的原理或者定位机制是什么？我们来探索一下，将公式再变一下：

=SUMIF(A2:A11,"龙逸凡",B3)

公式结果为21,为什么是21呢？实际上它是2+4+7+8的结果。A2：A11单元格区域为“龙逸凡”的分别为从A2单元格开始数的第1、3、6、7个，求和的单元格刚好也是由B3单元格开始数的第1、3、6、7个，即B3、B5、B8、B9单元格。

同理，=SUMIF(A2:A11,"龙逸凡",B4)结果为25，为B4单元格开始数的第1、3、6、7个，即B4、B6、B9、B10单元格。据此可知：

第三参数单元格区域起作用的就是左上角那单元格，此单元格的作用是定位定点，只要有此定位点，SUMIF会自动以此单元格为原点，按照第一参数区域符合条件的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。

此规律同样适用多列区域或矩形区域，示例参见后文。

四、多条件求和

在搞清楚第三参数的定位原理后，我们先来看一下如何用SUMIF进行简单的多条件求和，然后再研究多列、多表格求和。

我们知道，一般情况下，SUMIF只能单条件求和，如果要多条件求和，那怎么办呢？

1、多列多条件求和

遇到此情况，如果不使用SUM数组公式、SUMIFS或SUMPRODUCT函数,要用SUMIF来多条件求和的话，则需要使用辅助列，将需要条件判断的字段用连接符连接起来，将多列的多条件变为单条件，然后使用类似：=SUMIF(sheet1!F1:F1000,A1&B1&C1,sheet1!D1:D1000)的公式进行求和。这不是本文讨论的话题，就不举例了。

2、单列多条件求和

如上图一，如果统计A列龙逸凡和罗惠民的B列金额之和，则公式为：

=SUM(SUMIF(A2:A11,{"龙逸凡","罗惠民"},B2:B11))

结果为25。此公式使用常量数组将两个条件逐一传递给SUMIF，然后再使用SUM来统计各条件结果之和。

如果要统计龙姓员工和罗姓员工且不包含罗惠民的金额之和（36），公式为：

=SUM(SUMIF(A2:A11,{"龙*","罗*","罗惠民"},B2:B11)*{1,1,-1})

【提示】：

此公式不必使用CTRL+SHIFT+ENTER键来输入，和普通的公式一样输入就行了。

五、多行或多列的多条件求和

SUMIF是否只能单行单列条件求和？非也，还可多行或多列条件求和，我们仍以图一为例，求A1：D11区域龙逸凡的金额之和，公式为

=SUMIF(A2:D11,"龙逸凡",B2)

结果为121。

【提示】：

不能写成=SUMIF(A2:D11,"龙逸凡",A2：D11)，至于为什么，请细读一下本文的第三点“定位原理”。

我们将数据区域再变一下，以帮助大家更深入理解SUMIF，请看图二

如果要统计A1：D11区域龙逸凡对应的金额之和，公式为：

=SUMIF(A2:B11,"龙逸凡",C2:D11)

=SUMIF(A2:B11,"龙逸凡",C2)

结果为161。

如果要统计A1:D1区域龙逸凡和罗惠民对应金额之和呢？公式为：

=SUM(SUMIF(A2:B11,{"龙逸凡","罗惠民"},C2))

结果为206。

六、多表单条件求和

假设有三张表，分别为sheet1、sheet2、sheet3，三张表格式均如图一所示，要求三表中A列为龙逸凡的金额之和，公式为：

=SUM(SUMIF(INDIRECT("sheet"&{1,2,3}&"!A2:A11"),"龙逸凡",INDIRECT("sheet"&{1,2,3}&"!B2:B11")))

结果为51（17*3）。

作者：龙逸凡

【老朋友】→请点击右上角的按钮，将本文分享到朋友圈。

【新朋友】→请点击标题下的ExcelHome，添加关注。或者直接查找公众号iexcelhome