01, 单条件求和
02, 排除某个条件求和
03, “并且”关系求和
04, “或”关系求和
05, 月份计算求和
06, 隔列按行计算求和
07, 大于平均值求和
08, 前N名求和
09, 排除错误值求和
10, 去除小计求和
11, 包含某个关键字求和
12, 开头为指定字符求和
13, 结尾为指定字符求和
14, 按字符长度求和
15, 忽略通配符求和
16, 多列数据计算求和
17, 准确的隔列数据计算求和
18, 多表计算求和
NO.1 单条件求和
公式：
=SUMIF(A:A,E2,C:C)
这是工作中最常见的问题之一，也是SUMIF最基础的用法，摊手，没啥好解释的。
NO.2 排除某个条件求和
如下图所示，需要在F2单元格计算地区为非“华中”的销售总额。
公式：
=SUMIF(A:A,"<>华中",C:C)
公式使用运算符不等号<>，排除掉“华中”地区，以此作为SUMIF的求和条件。除了不等于号，SUMIF同样支持>、>=、<、<=等运算符号。
比如计算C列金额大于等于60的总金额，公式如下：
=SUMIF(C:C,">=60")
NO.3 “并且”关系求和
如下图所示，需要在G列计算同时满足E列地区和F列项目的销售额总计，公式如下：
NO.4，“或”关系的求和
如下图所示，需要在F2单元格计算项目为“看见星光”和“Excel”的销售金额总和，也就是“或”关系的求和问题。
公式如下：
=SUM(SUMIF(B:B,{"诸葛亮","项羽"},C:C))
公式使用常量数组构建了多个值作为SUMIF的求和条件：{"诸葛亮","项羽"}。
SUMIF会依次对每个条件进行运算，于是得到多个求和结果，比如此例计算结果为：{142,138}。
最后使用SUM函数对其返回的多个结果进行求和即可。
NO.5，按指定月份求和
如下图所示，需要在F2单元格计算月份为8月份的销售金额总和。
我们上一章讲过，SUMIF(S)条件区域和求和区域均不支持除了单元格引用以外的任何形式，比如数组和常量，因此本例中无法使用以下函数公式计算结果。
=SUMIF(MONTH(A:A),8,C:C)
正确的计算公式如下：
=SUMIFS(C:C,A:A,">=2019-8-1",A:A,"<=2019-8-31")
公式将一个貌似单条件求和问题演变成了多条件求和问题，准确描述出8月份的起始日和截至日，使公式计算A列月份即大于等于8月1号（">=2019-8-1"），又小于等于8月31号的销售金额之和（"<=2019-8-31"），实际也就是了计算8月份销售金额的总和。
NO.6，隔列按行计算求和
如下图所示，需要在H列和I列分别计算每一行记录中出库和入库的数量总和。
SUMIF函数公式如下：
=SUMIF(2:
2,I
B3:$G3)
SUMIF不但能按列条件求和，也能按行条件求和。公式将绝对引用状态下的标题行2:
2作为条件区域，求和区域采用了列绝对引用行相对引用的混合引用，比如
G3，当公式向下向右复制，列标不变，向下复制时行号改变，比如
G4，
G5……以此达到正确引用求和区域的目的。
NO.7，大于平均值求和
如下图所示，需要在F2单元格计算C列销售额大于平均销售额的总和。
公式如下
=SUMIF(C:C,">"&AVERAGE(2:
11))
公式使用AVERAGE函数计算出C列金额的平均值，以此作为SUMIF的求和条件。SUMIF忽略第3参数求和区域，按我们上一章所说，会自动将条件区域视为求和区域。
NO.8，前N名数据求和
如下图所示，需要在F2单元格计算C列销售额前三名总和。
公式如下：
=SUMIF(C:C,">="&LARGE(C2:C11,3))
公式使用LARGE函数计算出C列金额第三项最大值，以大于等于该值作为SUMIF函数的求和条件，也就是计算前三名最大值的总和。
如果需要计算后三名最小值的总和，公式如下：
=SUMIF(C:C,"<="&SMALL(C2:C11,3))
NO.9，排除错误值求和
如下图所示，需要在C12单元格计算C列的金额总和。
由于C列含有错误值，如果直接使用SUM函数，计算结果会返回错误值。
通常使用SUMIF函数如下：
=SUMIF(C2:C11,"<9E307")
同样的问题，如果我们需要计算地区为“华东”，项目为“透视表”的金额总和，如果使用以下公式会返回错误值，原因我们上一章讲过了。
=SUMIFS(C:C,A:A,"华东",B:B,"萧何",)
同样的套路，使用以下公式即可获取正常结果。
=SUMIFS(C:C,A:A,"华东",B:B,"萧何",C:C,"<9e307")
NO.10，去除小计求和
如下图所示，需要在C15单元格计算C列金额总和。
这样的总值计算方法有两种，一种是只计算小计行的汇总值，一种是只计算明细值之和。
正常情况下我们可以使用公式：
=SUMIF(A2:A14,"小计",C2:C14)
或者公式：
=SUM(C2:C14)/2
但问题是，如果有人小计行没填写，如本示例情况，以上两个公式都将返回错误结果。
稳妥的公式还是下面这一条，用C2:C14的全部金额减去小计行金额即为正确的总计金额：
=SUM(C2:C14)-SUMIF(A2:A14,"小计",C2:C14)
NO.11，包含某个关键字求和
如下图所示，需要在F2单元计算项目包含关键字"EXCEL"的金额总和。
公式如下：
=SUMIF(B:B,"韩信",C:C)
通配符星号可以代表0到任意多个字符，"韩信"，也就可以代表包含“韩信”的字符串。以此作为SUMIF的查询条件，即可获取包含"韩信"关键字的项目金额总和。
NO.12，开头为指定字符求和
如下图所示，需要在F2单元计算项目以"P"开头的总金额。
公式如下：
=SUMIF(B:B,"h*",C:C)
公式同样使用了通配符星号，"h*"也就是以"h"开头的任意字符串。
NO.13，结尾为指定字符求和
如下图所示，需要在F2单元计算项目以"表"结尾的总金额。
公式如下：
=SUMIF(B:B,"*代",C:C)
公式同样使用了通配符星号，"*代"也就是以"表"结尾的任意字符串。
NO.14，按字符长度求和
如下图所示，需要在F2单元计算项目长度为4个字符的总金额。
屏幕快照 2020-05-03 上午12.34.34.png
公式如下：
=SUMIF(B:B,"??",C:C)
在Excel中，问号作为通配符，只代表一个字符，"??"，2个问号也就代表了字符串长度为2，以此作为SUMIF的求和条件，即可获取项目长度为2个字符的对应总金额。
NO.15，忽略通配符求和
就像那个人一样，通配符有被需要的时候，也有被嫌弃的时候。当单元格中出现通配符时，我们就希望不要把它当通配符看待，而只是视为一个正常的值，此时我们可以将相应的通配符，替换为“~通配符”。
如下图所示，需要在E2单元格计算产品编号为123*456的销售数量。
如果使用以下公式，将会返回错误结果。
=SUMIF(A:A,D2,B:B)
SUMIF函数的查询条件是D2，D2单元格的值是"123*456"，由于通配符星号的存在，系统误认为我们是要计算123开头，456结尾的产品编码数据，也就会将示例图片中全部产品的销量作为计算结果。
使用以下函数公式可以获取正确结果。
=SUMIF(A:A,SUBSTITUTE(D2,"","~"),B:B)
NO.16，多列数据计算求和
如下图所示，A:F列是数据范围，需要在I列计算H列相关项目的销售额总和。
公式如下：
=SUMIF(A:E,H2,B:F)
SUMIF的查找区域是多列区域A:E列，依照我们上一章讲述过的SUMIF运算规则，实际运算查找区域为A1:F11，同样依照我们上一章讲述过的SUMIF运算规则，求和区域B:F也好，B:B也好，B:J也好，在实际运算中都并不重要，重要的仅仅只是指定正确的首个求和单元格。
也许将公式改写为以下形式更有助于你对本条函数的理解：
=SUMIF(A:E,H2,1)
如我们上一章所讲，SUMIF函数通过查找区域的首个单元格A1和求和区域的首个单元格B1，构建查找区域和求和区域的位置关联关系，并按照多退少补的原则对求和区域自行增减。
本例中查找区域和求和区域的关联关系也就是A1对应B1，A2对你应B2，B1对应C1，B2对应C2，C1对应D1，C2对应D2依次类推……
也就是说这条SUMIF函数实际上并不是隔列查询，比如分别查找A2:A11、C2:C11、E2:E11列的姓名是否等于“刘备”，而是查找A2:E11单元格范围内每一个单元格（包含金额列）是否等于“刘备”，并对符合查找条件的右一单元格的值进行求和。
如下图所示，A/C/E列是原始得分，B/D/F列是修正得分，现需计算原始得分大于60分的，对应修正得分总和……
想一下，公式：
=SUMIF(A:E,">60",B:F)
能得出正确的计算结果吗？
当然是不能。
如果解决这样的问题呢？
NO.17，准确的隔列数据计算求和
当碰到上一题所示的问题时，我们通常使用以下公式：
=SUM(SUMIF(OFFSET(A:A,,{0,2,4}),">60",OFFSET(A:A,,{1,3,5})))
公式使用OFFSET函数构建条件区域和求和区域。OFFSET(A:A,,0)也就是A列向右偏移0列，还是A列。OFFSET(A:A,,2)，A列向右走两列，也就是C列……OFFSET(A:A,,{0,2,4})，也就是由A列C列E列组成的多维区域，需要说明的是，并不是每一个函数都支持多维引用，SUMIF函数属于少数支持者之一。
SUMIF函数计算出每一个维度符合条件的求和结果，最后由SUM函数汇总求和。
NO.18，多表计算求和
如下图所示，有三张工作表，名称依次为华南、华中、华东。
现在需要以下表的B列计算A列相应产品在华南、华中、华东三表的金额总和。
由于三张分表项目的排列顺序并不一致，也就无法使用我们在SUM函数篇所介绍的SUM多表求和套路。
正确公式如下：
=SUM(SUMIF(INDIRECT({"华南","华中","华东"}&"!a:a"),A2,INDIRECT({"华南","华中","华东"}&"!b:b")))
公式有些类似于示例17，不同的是示例17使用OFFSET搭建同一张工作表不同的单元格引用，而本例则是由INDIRECT函数搭建不同的工作表相同的单元格引用。
INDIRECT是引用类函数，我们以后会讲到，这里只是简单的介绍两句，先留个印象，不久的将来再见面时好说话些。
学习INDIRECT只需要记住以下两点。
其一，它的参数是一个地址。这个地址可以由常量组成，比如INDIRECT("A1")，双引号包括起来的"A1"是一个文本常量。此外该地址也可以由变量产生，比如INDIRECT(A1)，A1没有使用双引号包起来，也就是指的A1单元格，此时INDIRECT所使用的地址是A1单元格保存的值。
其二，INDIRECT返回的是单元格的引用，而不是单元格的值。比如，假设A1单元格存在值“李逵”，公式=INDIRECT("A1")，返回的是A1单元格的引用，单元格引用包含了A1单元格本身的多种属性，比如地址、高度、宽度等，而值属性只是其中之一，被Excel默认为主要属性，并显示出来，也就是我们看到的"李逵”。
正是因为INDIRECT函数所返回的结果是单元格引用，所以它才可以作为SUMIF条件与求和区域的嵌套函数进行使用。
网友评论