COUNTIFS函数基础应用

基础语法

COUNTIFS函数的基础语法为：
COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)

其中，criteria_range代表要统计的条件区域，criteria代表要统计的参数，用来定义将对哪些单元格进行计数。

每一个criteria_range参数的区域都必须具有相同的行数和列数。这里要注意，它的参数是“成对”出现的。另外，COUNTIFS支持127对条件统计，这个知识点仅作了解即可，日常的工作不会用到这么复杂的条件。

分别对字符串、数字、日期进行统计

如图19-1所示，C11:F24单元格区域是基础数据源，C列为组别，D列为姓名，E列为销售日期，F列为销售金额，接下来对这部分数据做相应的统计。

图19-1数据源区域

1．案例：统计汉字

首先来统计一组的人数。换成Excel的语言，就可以翻译成C列有多少个单元格是“一组”。如图19-2所示，在I12单元格中输入公式：=COUNTIFS(C12:C24,”一组”)

图19-2统计汉字1

在统计汉字的时候，可以直接输入相应的文字，并在文字两侧加上英文状态下的双引号。这是统计一个组的情况，要想统计多个组，不用每个参数都手动输入，可以提前在单元格中输入相应的参数，如图19-3所示，H14:H16单元格区域是需要统计的组别信息，在I14单元格中输入以下公式并向下复制到I16单元格。
=COUNTIFS($C$12:$C$24,H14)

图19-3统计汉字2

它的计算过程：首先，引用H14单元格的值，将H14变为文本字符串”一组”；其次，公式变为“=COUNTIFS($C$12:$C$24,”一组”)”；最后，进一步完成统计。
这里再次提示，只要涉及公式复制，就一定要想到“图钉”的问题。以上就是最基础的COUNTIFS函数的统计。

2．案例：统计数字

条件统计函数不仅可以统计汉字，还可以统计数字。下面对数据源中F列的销售金额进行统计，分别统计“大于5000”“等于5000”“小于等于5000”的条件下各有多少人。在I19单元格中输入公式：=COUNTIFS($F$12:$F$24,”>”&5000)

可以看到数据源F列中有5个是大于5000的。在统计数字的时候，通过添加比较运算符统计数字的范围。注意一个细节，这里COUNTIFS的第2个参数使用的是”>”&5000，将比较运算符和数字两部分分开，中间用“胶水”（&）连接。那么，此处是否可以不用&，直接连在一起呢？

当然可以，公式可以写成“=COUNTIFS($F$12:$F$24,”>5000″)”，但是在函数公式初学阶段，连在一起写很容易出现错误，本节后面会讲到这个问题。如果碰到比较运算符号，还是建议将它与相应的参数分开写。

在I20单元格中输入以下公式，统计等于5000的人数：=COUNTIFS($F$12:$F$24,”=”&5000)

将比较运算符和参数分开写，当然在统计“等于”的时候可以将等号去掉，变为：
=COUNTIFS($F$12:$F$24,5000)

在I21单元格中输入以下公式，统计小于等于5000的人数：=COUNTIFS($F$12:$F$24,”<=”&5000)
计算结果如图19-4所示。

图19-4统计数字1

提示：并不是只有统计数字的时候可以使用比较运算符，统计汉字的时候也是可以的，如公式“=COUNTIFS(D12:D24,”>”&”徐庶”)”，返回结果为2，因为汉字一般是根据每个字的汉语拼音读法，按照26个英文字母的顺序从小到大排列的，数据源中比“徐庶”大的有“许褚”和“张飞”，所以结果为2。不过工作中很少会用到这种方式统计汉字。
做数字统计时，不仅可以直接在公式中输入条件，还可以将条件放在单元格中，然后直接引用，如图19-5所示，H23:H25单元格区域分别为“>5000”“5000”“<=5000”。在I23单元格中输入以下公式，并向下复制到I25单元格，完成相应的数据区间的统计。=COUNTIFS($F$12:$F$24,H23)

图19-5统计数字2

可以看到与之前的统计结果完全一致，这样做有一个好处，以后如果需要修改统计条件，可以不用修改公式，直接在H23:H25的相应单元格中修改即可，既直观又快捷。在实际工作中，也尽量把问题考虑全面，做到函数公式一步到位，以后只需在表格相应的参数区域修改就可以。
继续看一种统计数据的方式，如图19-6所示，在H27单元格中输入统计的分隔点，数字5000，然后还是分别统计“大于”“等于”“小于等于”三组数字。在I27单元格中输入公式：
=COUNTIFS($F$12:$F$24,”>”&H27)

图19-6统计数字3

注意观察，这就涉及之前埋的伏笔，为什么要求大家将比较运算符和参数分开写。很多人会将公式写成“=COUNTIFS($F$12:$F$24,”>H27″)”。公式乍一看，好像没问题，可是这个公式返回的结果为0。为什么呢？这就要说一下“活性”的问题了。

H27没有在双引号中，它保持了自己的“活性”，代表引用的是相应单元格，而一旦把它放在了双引号中，它就变成了一个“木乃伊”，不再具有“活性”。”>H27″统计的并不是大于H27单元格的那个数字5000，而是大于“H27”这3个字符的数据。在COUNTIFS的统计中，它先判断条件的数据类型，发现数据类型是文本，而F12:F24单元格区域中全都是数字，没有文本，所以结果为0。

我们使用函数是为了减少错误，所以在对函数尚不熟悉的情况下，把比较运算符和参数分开写，中间用“胶水”（&）粘在一起，这样能减少70%的错误。
继续完成另外两个统计，在I28单元格和I29单元格分别输入公式：=COUNTIFS($F$12:$F$24,”=”&H27)
=COUNTIFS($F$12:$F$24,”<=”&H27)

当统计修改为以3000为分隔点的时候，只需将H27单元格修改为3000，其他公式完全不用改动，就能完成工作，如图19-7所示。

图19-7修改参数

3．案例：统计日期

下面继续看统计日期的方式，为了方便查看页面，我们在C33:F46单元格区域建立相同的数据源，如图19-8所示。

图19-8数据源区域

统计销售日期在2016年2月的人数。先把公式写下来，再慢慢分析，如图19-9所示，在I33单元格中输入公式：=COUNTIFS($E$34:$E$46,”>=”&”2016-2-1″,$E$34:$E$46,”<“&”2016-3-1”)

图19-9统计日期1

这个函数需要注意以下几个方面。

（1）回顾下第10章讲的日期函数，日期和时间的本质就是数字。统计某一区间日期，就相当于统计两个数字之间的数量，于是用到了“掐头去尾”的方式。

（2）这种快速输入日期的方式，必须用英文状态下的双引号引起来，否则它不表示日期，而只是一个普通的数字减法。如果这种方式掌握不好，那就规规矩矩使用DATE函数，如DATE(2016,2,1)，可以减少错误。

（3）这个数据源中的数据都是日期，不包含时间的部分，所以用“”>”&”2016-1-31″”“”<=”&”2016-2-29″”等不同的固定首、尾的方式都可以。但如果数据源中的数据含有时间，如“2016-1-3115:28”“2016-2-2909:07”等，则必须采用公式中“>=本月的1日<下个月1日”的日期方式，这样的统计是最准确的。就好像统计分数的时候，80分到90分之间为良，如果大家的得分都是整数，那么“>79”“<=89”等方式都可以，但当分数中包括79.5、89.5等小数的时候，只能用“>=80”“<90”来表达最准确的区间。

（4）COUNTIFS可以多次对同一区域进行引用。有人问：“统计的公式太长了，可不可以用MONTH函数把日期的月份提取出来，然后用COUNTIFS函数统计其中有多少月份等于2？” 我们动手试一下就知道答案了。按照此方法写下公式“=COUNTIFS(MONTH(E34:E46),2)”，然后按下【Enter】键，系统出现了错误提示，如图19-10所示。图19-10公式错误提示公式的逻辑没有问题，那到底是哪里出错了呢？ MONTH(E34:E46)的结果是{2;2;2;3;2;2;3;3;3;3;1;2;2}，这是一个数组，而COUNTIFS中的第1个参数是criteria_range。注意，“range”的意思是一个区域，所以COUNTIFS的第1，3，5，7，…参数是不支持数组的，必须是区域，即必须是在Excel表格中画出来一片单元格区域。与它有相同要求的参数还有ref、reference。有了第一个统计日期的基础，我们继续操作。统计的时候，不可能每一个月份都手动输入，更多的情况是在单元格中输入1月、2月、3月等内容，然后完成相应的统计，如图19-11所示。在I36单元格中输入以下公式，并向下复制到I38单元格：=COUNTIFS($E$34:$E$46,”>=”&DATE(2016,LEFTB(H36,2),1),$E$34:$E$46,”<“&DATE(2016,LEFTB(H36,2)+1,1))