excel函数公式大全sum怎么使用（穿越时间Excel升级之路连载7）
excel函数公式大全sum怎么使用（穿越时间Excel升级之路连载7）连载6中我们提到了神级函数SUMPRODUCT，它本身支持数组运算，当然不是数组的单值运算也不在话下，神级函数有多神？如果以我们生活的三维空间举例，sumproduct堪称四维空间的高级文明，对我们的日常应用实属降维打击。今天我们要讨论的是Excel中的条件计数，分为四个部分（单条件计数、单条件计数的和；多条件计数，多条件计数的和），每个部分我都会讨论十余种解决方案！ 穿越时间·Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥 穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧 穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较 穿越时间•Excel升级之路连载5：数组的理想照进现实
跟我一起，穿越时间！
回顾一下，经过前面6期的连载，相信你的Excel水平已经有了肉眼可见的提高，Excel基础理论也变得扎实稳固，俗话说“基础不牢，地动山摇”，在正式实际应用之前，如果你还没有仔细看过前面的连载，可以点击头像或链接跳转：
穿越时间•Excel升级之路连载1：Office2010安装体验及个性设置
穿越时间•Excel升级之路连载2：打牢Excel 2010中的数据类型基础
穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧
穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较
穿越时间•Excel升级之路连载5：数组的理想照进现实
穿越时间·Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥
连载6中我们提到了神级函数SUMPRODUCT，它本身支持数组运算，当然不是数组的单值运算也不在话下，神级函数有多神？如果以我们生活的三维空间举例，sumproduct堪称四维空间的高级文明，对我们的日常应用实属降维打击。今天我们要讨论的是Excel中的条件计数，分为四个部分（单条件计数、单条件计数的和；多条件计数，多条件计数的和），每个部分我都会讨论十余种解决方案！
一起走到Excel升级之路连载7：SUMPRODUCT函数挑战Excel条件计数。
条件计数是什么？相信大家都不陌生，就是按条件统计数目，或者是统计符合一个条件或多个条件的单元格的个数。它是条件基础上的计数。
在Excel中，能够实现条件计数的方法非常多，各种方法可一览如下。
1、COUNT函数
Excel中可以找到5个和计数相关的函数，他们都以count开头。
前三个count、counta、countblank是最基本的计数函数，我们可以用它们来计算区域中包含数字的单元格个数、非空单元格个数和空单元格个数。
后两个countif、countifs是单条件计数函数和多条件计数函数，通过他们，我们可以得到满足某个条件或满足某几个条件的单元格数量。
2、SUM函数
SUM函数只是求和函数吗？不要以为sum函数是不起眼的小学生函数，整合我们前面连载讲过的运算顺序和数组之后，sum函数也可以用来进行条件计数。
3、sumproduct函数
既然名声在外，那必须实至名归，sumproduct说还有什么我不能计算的呢。
4、筛选
筛选方法是人工方法，如果说上面的各种函数方法是工业文明流水线，那么筛选方法则是农业文明小农经济精耕细作，但是通过筛选来进行条件计数也最直观的，可谓所见即所得。
下面，我们一起开始条件计数的挑战。
二、条件计数情境设置我们延续连载6中武林各派销售文创产品的情境，本系列连载中的销售情境数据均为演示学习需要而原创虚构。
下表包括不同的门派、销售人员和文创产品，同时列出来的还有销售数量和单价情况。
在销售文创产品的过程中，周婉悦成功开单的日期共有几天？
（1）方法一：筛选法
表格中每一行都代表一天开单日期，想知道周婉悦成功开单的日期有几天，即周婉悦开了几次单，只需在D列销售人员列筛选“周婉悦”即可。
我们可以非常直观地看到周婉悦成功开单的日期有3天。
为了实现筛选后Excel自动计数，注意我在D21单元格中使用了函数公式，来实现筛选后的自动更新计算，并展示结果。公式为：
=SUBTOTAL(2 H2:H18)
SUBTOTAL用于分类汇总，函数可以返回列表或数据库中的分类汇总，语法是：SUBTOTAL(function_num ref1 [ref2] ...])
其第一个参数指定汇总函数，例如求平均值、计数、求和等，后面的参数则为计算区域，因为现在要数出筛选后符合条件的数目，所以使用的是计数功能，第一个代码指定为2，计算区域为销售人员H2:H18，这样我们进行筛选，即可实现实时根据筛选结果自动计算。关于这个函数的更多用法，这里不展开。
（2）方法二：COUNTIF函数
通过countif可以计算某个区域中满足给定条件的单元格数目，在这里很显然就是计算销售人员区域D2:D18中是“周婉悦”的单元格数目。
Countif函数非常简单，COUNTIF(range criteria)，第一个参数是区域范围，第二个参数是条件，注意条件中要使用英文引号，输入文本字符型的周婉悦或者引用包含周婉悦的单元格。
=COUNTIF(D2:D18 "周婉悦")
=COUNTIF(D2:D18 D2)
我们可以看到函数直接计算出来结果是3
当然，这里使用countifs也是可以的，不过因为这一部分是单条件计数，所以没有必要使用countifs。
（3）方法三：SUM函数
Sum函数也能数出周婉悦成功开单的日期数？来看能否理解下面的公式：
=SUM((D2:D18="周婉悦")*1)
=SUM((D2:D18=D2)*1)
按下Ctrl Shift Enter执行数组运算，公式的结果也是3，为什么？
如果看不明白公式的意思，说明前面连载修炼还不到位，请返回继续学习。
解释：
D2:D18=D2实质为一个逻辑判断，因为是数组运算，所以会依次判断D2:D18中每一个单元格里的销售人员是否是“周婉悦”（D2=D2吗、D3=D2吗、D4=D2吗……），如果是则返回逻辑值TRUE，如果不是则返回逻辑值FALSE
由此构成一个数组，TRUE就代表一个周婉悦成功开单的日子；
那么只需要知道数组中TRUE有几个就行了。
(D2:D18=D2)*1
为什么要*1，原因是sum函数会忽略计算完成的单个数组参数中的逻辑值，无论TRUE还是FALSE都会被当做0（这点我在连载5、连载6讨论过），所以需要用*1的方法将逻辑值转换为0或1
这样数组就变为{1;0;1;0;0……0}
数组中的1就代表一个成功开单的日子；
然后sum函数对这个数组求和，实质是把数组里所有的元素相加求和，有多少个1就有多少个成功开单的日子。
这样，sum函数和数组一结合也实现了单条件计数。
（4）方法四：sumproduct函数
既然是sum函数可以完成的事，那sumproduct函数自然毫无问题，而且更加简洁。
=SUMPRODUCT((D2:D18="周婉悦")*1)
=SUMPRODUCT((D2:D18=D2)*1) 按Enter键即可
原理和上面利用sum函数的计算并无太多差异，
D2:D18=D2判断D2:D18中销售人员是否是“周婉悦”，返回逻辑值构成的数组；
(D2:D18=D2)*1把返回的逻辑值转变为数值（SUMPRODUCT函数也会忽略计算完成的单一数组参数中的逻辑值，这在连载6中讨论过）；
最后由于sumproduct可以直接处理数组，因此按Enter键即可计算。
2、单条件计数的和
我们升一下级，现在求一下周婉悦和玉玲师太成功开单的日子共有几天？
这可以理解为单条件计数的和的问题。
（1）方法一：筛选法
周婉悦和玉玲师太成功开单的日子，只需在D列销售人员中筛选“周婉悦”和“玉玲师太”即可。
我们可以看到答案是4天。
（2）方法二：countif函数
求周婉悦和玉玲师太成功开单的日子共有几天，就是求周婉悦成功开单的日子 玉玲师太成功开单的日子，那么公式可以写成：
=COUNTIF(D2:D18 "周婉悦") COUNTIF(D2:D18 "玉玲师太")
=COUNTIF(D2:D18 D2) COUNTIF(D2:D18 D18)
结果为4
这种分写累加的方法很容易理解，但是存在限制，我们继续往下看。
（3）方法三：sum函数
按照上面countif函数的思路，把周婉悦成功开单的日子和玉玲师太成功开单的日子加起来，使用sum函数法可以写为：
分写累加：周婉悦成功开单的日子 玉玲师太成功开单的日子
=SUM((D2:D18="周婉悦")*1) SUM((D2:D18="玉玲师太")*1)
=SUM((D2:D18=D2)*1) SUM((D2:D18=D18)*1)
按Ctrl Shift Enter计算，结果为4
或者换种思路，考虑为求满足周婉悦或玉玲师太成功开单的日子：
=SUM(((D2:D18="周婉悦") (D2:D18="玉玲师太"))*1)
=SUM(((D2:D18=D2) (D2:D18=D18))*1)
=SUM((D2:D18="周婉悦") (D2:D18="玉玲师太"))
=SUM((D2:D18=D2) (D2:D18=D18))
以上为SUM数组方法中的单参数写法，加号 体现或的意思（连载4精通公式运算规则和字符比较中有详细介绍）。
但如果现在要求周婉悦、玉玲师太、琼英、王孙药师……等等n个人成功开单的日子，分写累加的方法就不太合适了，因此，借助数组，我们有了一种合写的办法：
合写：
=SUM((D2:D18={"周婉悦" "玉玲师太"})*1) 按Ctrl Shift Enter计算
这里用到的是异向一维数组的逻辑运算，构建二维数组的办法，如果不理解请复习前面的连载内容。
解释一下：
D2:D18={"周婉悦" "玉玲师太"} D2:D18是一个由销售人员构成的纵向一维数组，{"周婉悦" "玉玲师太"}是由目标条件周婉悦和玉玲师太构成的横向一维数组，二者进行逻辑判断，返回一个由逻辑值构成的二维数组（这是数组的运算规则，连载5数组的理想照进现实中详细讲过）；
这个由逻辑值构成的二维数组里面的TRUE即代表周婉悦或玉玲师太成功开单的日子，求出TRUE的个数即可。
那么由于sum忽略单参数数组中逻辑值得缘故，所以需要(D2:D18={"周婉悦" "玉玲师太"})*1转化一下数据类型，然后通过sum计算1的个数就是答案。
注意=SUM((D2:D18={"周婉悦" "玉玲师太"})*1)中我使用的是常量数组{"周婉悦" "玉玲师太"}，而不是单元格引用（D2和D18并不是相邻的），如果写成=SUM((D2:D18={D2 D18})*1) 则错误。大括号{}内表示常量数组，不能放入单元格地址。
如果要使用单元格引用，则必须是横向连续的“周婉悦”“玉玲师太”两个单元格。
有的人说这里就所需要准备辅助列了，例如借用上图K1、L1单元格写成：=SUM((D2:D18=K1:L1)*1)
其实不必，我们这种合写的办法还可以再次升级到更高级的层面（如果你仔细看过连载5：数组的理想照进现实，你会想到构建虚拟内存数组）
合写再进一步，虚拟构建：
=SUM((D2:D18=IF({1 0} D2 D18))*1) 按Ctrl Shift Enter计算
利用If函数、数组{1 0}把不相邻的D2和D18构建为横向的内存数组，避免直接写出常量数组{"周婉悦" "玉玲师太"}
=SUM((D2:D18=CHOOSE({1 2} D2 D18))*1) 按Ctrl Shift Enter计算
利用Choose函数、数组{1 2}把不相邻的D2和D18构建为横向的内存数组，避免直接写出常量数组{"周婉悦" "玉玲师太"}
以上两种方法属于构建的高级用法，如果不理解，请仔细阅读之前的连载内容。
（3）方法四：sumproduct函数
如果上面sum函数的各种情况都能够理解的话，那么换用sumproduct函数将会更加简洁高效。
分写累加：
周婉悦成功开单的日子 玉玲师太成功开单的日子
=SUMPRODUCT((D2:D18="周婉悦")*1) SUMPRODUCT((D2:D18="玉玲师太")*1)
=SUMPRODUCT((D2:D18=D2)*1) SUMPRODUCT((D2:D18=D18)*1)
或者考虑为求满足周婉悦或玉玲师太成功开单的日子：
=SUMPRODUCT((D2:D18="周婉悦") (D2:D18="玉玲师太"))
=SUMPRODUCT((D2:D18=D2) (D2:D18=D18))
合写：
=SUMPRODUCT((D2:D18={"周婉悦" "玉玲师太"})*1)
同样，这里也用到了异向一维数组的逻辑运算构建二维数组，如果不理解请复习前面的内容。
合写再进一步，虚拟构建：
=SUMPRODUCT((D2:D18=IF({1 0} D2 D18))*1) 把不相邻的D2和D18构建内存数组，避免直接写出常量数组{"周婉悦" "玉玲师太"}
=SUMPRODUCT((D2:D18=CHOOSE({1 2} D2 D18))*1) 把不相邻的D2和D18构建内存数组，避免直接写出常量数组{"周婉悦" "玉玲师太"}
最终sumproduct函数完成条件计数、条件计数的和就是这么简洁，你学会了吗？上面共有十余种不同的方法，如果没有问题，就可以继续整体升级到下一部分。
四、多条件计数问题1、多条件计数：首先需要清楚多条件计数不等同于单条件计数的和！
单条件计数的和：条件是并列的，“或”的意思，满足其一即是；
多条件计数：条件是递进的，“并且”的意思，都要同时满足。
问：周婉悦成功开单卖出玉香剑的日期共有几天？
（1）方法一：筛选
问周婉悦成功开单卖出玉香剑的日期数，要求销售人员满足“周婉悦”，同时文创产品满足“玉香剑”；
只要在D列销售人员中筛选“周婉悦”，然后在E列“文创产品”中筛选“玉香剑”即可。
共有2天
（2）方法二：countifs多条件计数
销售人员得是“周婉悦”，周婉悦卖出的东西得是“玉香剑”，这是一种递进的2个限制条件，可以通过countifs函数来实现。
COUNTIFS(criteria_range1 criteria1 [criteria_range2 criteria2]…)
COUNTIFS(区域1，区域1要满足的条件，区域2，区域2要满足的条件……)
=COUNTIFS(D2:D18 "周婉悦" E2:E18 "玉香剑")
=COUNTIFS(D2:D18 D2 E2:E18 E2)
这个其实非常简单。
（3）方法三：SUM函数
SUM函数其实也能做到的：
=SUM((D2:D18="周婉悦")*(E2:E18="玉香剑"))
=SUM((D2:D18=D2)*(E2:E18=E2))
按Ctrl Shift Enter计算
(D2:D18=D2)、(E2:E18=E2)分别为两个条件判断，执行数组计算后返回两个由逻辑值构成的数组，二者相乘，即可以满足同时找出“周婉悦”卖“玉香剑”的情况，又可以实现逻辑值到数值型值的转换。
因此这里没有必要写成以下形式：=SUM(((D2:D18=D2)*1)*((E2:E18=E2)*1)) 如果不理解请看前面的内容。
下图可以体现，只有周婉悦卖玉香剑，两者都为TRUE的，最后相乘得到1才会被最终计数；
周婉悦卖其他东西或者其他人卖玉香剑，都会被某一个FALSE在乘法运算中清零，不会被最终计数。
（4）SUMproduct函数则加简洁
多参数写法：
=SUMPRODUCT((D2:D18="周婉悦")*1 (E2:E18="玉香剑")*1)
=SUMPRODUCT((D2:D18=D2)*1 (E2:E18=E2)*1)
(D2:D18=D2)*1和(E2:E18=E2)*1分别作为sumproduct的两个参数，分别找出“周婉悦”卖出东西的日子和其他人卖出“玉香剑”的日子，并通过*1将逻辑值数组转换为数值型的0或1，然后sumproduct函数将两个数组求积再求和，得到答案。
需要注意的是，如果写成：=SUMPRODUCT(D2:D18=D2 E2:E18=E2)，结果则为0，这是不对的，为什么？因为这种情况下sumproduct会把D2:D18=D2和E2:E18=E2返回的由逻辑值构成的数组（非数值元素）视为0，如果不理解请看前面的内容。
运算过程：
单参数写法
此外，如果利用=SUMPRODUCT((D2:D18=D2)*(E2:E18=E2))，单参数计算，也是可以的。
这里可以再考虑考虑sumproduct函数中的多参数写法和单参数写法，琢磨琢磨逗号和乘号的问题。
2、多条件计数的和：多条件计数的和其实又有两种情况，就是“和”在哪个条件上体现。
例如：
问：周婉悦和玉玲师太成功卖出玉香剑的天数？
问：周婉悦成功卖出玉香剑和西风刀的天数？
二者是不一样的，但都属于多条件计数的和。
我们来解决：周婉悦和玉玲师太成功卖出玉香剑的天数。
（1）方法一：筛选法
筛选销售人员：周婉悦、玉玲师太；筛选文创产品：玉香剑，答案为3
（2）方法二：COUNTIFS
周婉悦卖出玉香剑的天数 玉玲师太卖出玉香剑的天数
=COUNTIFS(D2:D18 D2 E2:E18 E2) COUNTIFS(D2:D18 D18 E2:E18 E2)
（3）方法三：SUM函数
分写累加：
周婉悦卖出玉香剑的天数 玉玲师太卖出玉香剑的天数：
=SUM((D2:D18=D2)*(E2:E18=E2)) SUM((D2:D18=D18)*(E2:E18=E2))
按Ctrl Shift Enter计算
或者换种思路，考虑求满足周婉悦或玉玲师太，卖出玉香剑的天数：
=SUM(((D2:D18=D2) ((D2:D18)=D18))*(E2:E18=E2))
合写：
=SUM((D2:D18={"周婉悦" "玉玲师太"})*(E2:E18=E2))
按Ctrl Shift Enter计算
异向一维数组逻辑运算构建二维数组，二维数组再与一维数组相乘，转换为数值数组再求和输出。
合写再进一步，虚拟构建：
=SUM((D2:D18=IF({1 0} D2 D18))*(E2:E18=E2)) 按Ctrl Shift Enter计算
=SUM((D2:D18=CHOOSE({1 2} D2 D18))*(E2:E18=E2)) 按Ctrl Shift Enter计算
（4）方法四：SUMPRODUCT函数
分写累加：
周婉悦卖出玉香剑的天数 玉玲师太卖出玉香剑的天数。
多参数分写：
=SUMPRODUCT((D2:D18=D2)*1 (E2:E18=E2)*1) SUMPRODUCT((D2:D18=D18)*1 (E2:E18=E2)*1)
单参数分写：
=SUMPRODUCT((D2:D18=D2)*(E2:E18=E2)) SUMPRODUCT((D2:D18=D18)*(E2:E18=E2))
按Enter计算
或者换种思路，考虑求满足周婉悦或玉玲师太，卖出玉香剑的天数：
=SUMPRODUCT(((D2:D18=D2) (D2:D18=D18)) ((E2:E18=E2))*1)
=SUMPRODUCT(((D2:D18=D2) (D2:D18=D18))*(E2:E18=E2))
合写：
单参数合写，或构建一下，可以用以下公式：
=SUMPRODUCT((D2:D18={"周婉悦" "玉玲师太"})*(E2:E18=E2))
=SUMPRODUCT((D2:D18=IF({1 0} D2 D18))*(E2:E18=E2)) 构建数组高级用法
=SUMPRODUCT((D2:D18=CHOOSE({1 2} D2 D18))*(E2:E18=E2)) 构建数组高级用法
最后，有人就要问了，那SUMPRODUCT的多参数合写行不行呢？
答案是不行！
=SUMPRODUCT((D2:D18={"周婉悦" "玉玲师太"})*1 (E2:E18=E2)*1) 报错#VALUE!
=SUMPRODUCT((D2:D18=IF({1 0} D2 D18))*1 (E2:E18=E2)) 报错#VALUE!
=SUMPRODUCT((D2:D18=CHOOSE({1 2} D2 D18))*1 (E2:E18=E2)) 报错#VALUE!
为什么！？
如果你真的基础扎实，这里出错的原因应该能想出来：
因为sumproduct要求参与计算的数组必须具有相同的维度；
(D2:D18={"周婉悦" "玉玲师太"})*1的结果会是二维数组；
(E2:E18=E2)*1的结果会是一维数组；
故SUMPRODUCT函数报错#VALUE!
最后，多条件计数的和的另一个问题，问：周婉悦成功卖出玉香剑和西风刀的天数，应该就不是问题了！
你学会了？还是学废了？
好了，以上就是连载7的全部内容，相信你对SUMPRODUCT函数、数组有了更深入的认识，如果有不理解的可以先看前面的连载打牢基础。
点击头像或链接跳转：
穿越时间•Excel升级之路连载1：Office2010安装体验及个性设置
穿越时间•Excel升级之路连载2：打牢Excel 2010中的数据类型基础
穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧
穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较
穿越时间•Excel升级之路连载5：数组的理想照进现实
穿越时间·Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥
更多精彩，敬请关注，投币赞赏，感谢支持。
（原创连载，个人观点保留，禁止任何未经授权的非本人账号复制文章到其他平台发布）
