COUNTIF与SUMPRODUCT函数过招!

Excel函数 2023-01-14

关于使用分隔符号-会出错的问题，这个是去年无意间发现的，这点希望所有人记住。详见文章：这是我此生见过COUNTIF函数，最奇葩的错误！

COUNTIF和COUNTIFS函数很好用，SUMPRODUCT函数也不错。究竟谁更好用，一起来看看。

1.下面请看第一场比赛：如何统计值班经理的值班次数？

COUNTIF函数首先应战，在H2单元格输入公式，并向下填充。

=COUNTIF(A:A,G2)

COUNTIF函数语法：

=COUNTIF(条件区域,条件)

SUMPRODUCT函数也不甘示弱，在I2单元格输入公式，并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*1)

SUMPRODUCT函数单条件计数语法：

=SUMPRODUCT((条件1)*1)

或者

=SUMPRODUCT(–(条件1))

2.第一场比赛可谓势均力敌，不分胜负。下面请看第二场比赛：统计值班经理在中午时间段的值班次数。

两个条件？COUNTIF函数顿时傻眼了，多条件计数是COUNTIF函数心里永远的痛。然而，SUMPRODUCT函数却气定神闲，在H2单元格输入公式，并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*($B$2:$B$10=$H$1))

SUMPRODUCT函数多条件计数语法：

=SUMPRODUCT((条件1)*(条件2)*(条件n))

“打虎亲兄弟，上阵父子兵”，看到兄弟COUNTIF函数有难，擅长多条件计数的COUNTIFS函数果断出手了，在I2单元格输入公式，并向下填充。

=COUNTIFS(A:A,G2,B:B,$H$1)

COUNTIFS函数语法：

=COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)

3.第二场比赛的结果大家有目共睹，胜利属于SUMPRODUCT函数。下面请看第三场比赛：值班经理都用了一个字作为自己的简称，如何根据简称统计值班次数？

SUMPRODUCT函数没有了之前的淡定从容，陷入了沉思中。而COUNTIF函数却露出了久违的笑容，它拿出了自己的绝活，在H2单元格输入公式，并向下填充。

=COUNTIF(A:A,”*”&G2&”*”)

在这里，“*”代表通配符，表示任意一个或者多个字符。在Excel函数中，能与通配符配合使用的函数并不多，COUNTIF函数是其中的一个，当然也包括了COUNTIFS函数，SUMIF函数，SUMIFS函数，VLOOKUP函数，MATCH函数等等。

SUMPRODUCT函数想破了脑袋，借助其他函数，终于也统计出来了。

=SUMPRODUCT(–ISNUMBER(FIND(G2,$A$2:$A$10)))

这个公式比较复杂，下面我们按步骤来分析。

Step 01 先看最里层的FIND函数， FIND函数的语法：

=FIND(查找的字符,查找的地方)

在I2，I3单元格分别输入公式:

=FIND(“风”,”风清扬”)

=FIND(“风”,”东方不败”)

在第一个公式中，因为字符“风”在字符串“风清扬”的第一个位置，所以结果返回1。而第二个公式中，因为字符“风”没有在字符串“东方不败”中，所以结果返回错误值。

Step 02 熟悉了FIND函数的基本运用后，我们在I2单元格输入公式：

=FIND(G2,$A$2:$A$10)

我们知道，在“A2:A10”区域中，存在了两个“风清扬”，按道理，字符“风”是能查找到的，应该返回数字才对啊，但是却返回错误值，这究竟是为什么呢？

FIND函数的第二个参数是一个区域，所以返回的结果是若干个数据，多个数据放在一个单元格中，当然会出错了。这个时候，我们需要借助一个神器：独孤九剑，也就是F9键。选择公式所在单元格，点击编辑栏，按F9键。

Step 03 带有红色方框的数字个数就代表了该值班经理的值班次数。那么怎么统计数字的个数呢？可以使用ISNUMBER函数，如果是数字就返回TRUE，否则就返回FALSE。

=ISNUMBER(FIND(G2,$A$2:$A$10))

Step 04 再结合SUMPRODUCT函数，结果便出来了，公式在上面已经给出。

第三场比赛，虽然SUMPRODUCT函数最后完成了任务，但评委的眼睛是雪亮的，这一次，评委把票投给了COUNTIF函数。

比赛的结果并不重要，重要的是，在什么时候该使用什么函数。怎么简单怎么来，作为这次比赛的吃瓜观众，你们说呢？

上一篇：Excel必须学会函数之一,countif和SUMIF函数下一篇：COUNTIF与SUMIF函数的常见用法

COUNTIF与SUMPRODUCT函数过招!

热门文章

最新文章

标签列表