COUNTIF和SUMPRODUCT函数有一招!

关于使用分隔符-会出错的问题,这个是去年无意中发现的,希望大家记住。详见文章:这是我这辈子见过的COUNTIF函数最奇怪的错误!

COUNTIF和COUNTIFS函数运行良好,SUMPRODUCT函数也是如此。谁更好用?让我们来看看。

1.请看下面第一个游戏:如何统计值班经理的值班次数?

COUNTIF与SUMPRODUCT函数过招!

COUNTIF函数应该首先战斗,在单元格H2中输入公式,然后向下填充。

=COUNTIF(A:A,G2)

COUNTIF函数语法:

=COUNTIF(条件区域,条件)

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

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

SUMPRODUCT函数单条件计数语法:

=SUMPRODUCT((条件1)*1)

或者

= sum product(& # 8211;(条件1))

2.第一局可以说是势均力敌,没有胜负。再来看第二个游戏:统计值班经理中午的值班次数。

COUNTIF与SUMPRODUCT函数过招!

两个条件?COUNTIF函数立刻傻眼了,多条件计数是COUNTIF函数心中永远的痛。然而,SUMPRODUCT函数很冷静,在H2单元格中输入公式,并将其填满。

= sum product(($ 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函数。请看下面第三个游戏:值班经理都用一个字作为缩写。如何根据缩写统计值班次数?

COUNTIF与SUMPRODUCT函数过招!

SUMPRODUCT函数失去了之前的冷静,陷入了沉思。然而,COUNTIF函数露出了久违的笑容。它拿出它的独特技能,在细胞H2输入公式,并填写下来。

=COUNTIF(A:A,& # 8221;*”& ampG2 & amp”*”)

这里,“*”代表通配符,表示任意一个或多个字符。在Excel函数中,可以使用通配符的函数并不多。COUNTIF函数就是其中之一,包括COUNTIFS函数、SUMIF函数、SUMIFS函数、VLOOKUP函数、MATCH函数等等。

SUMPRODUCT函数试图打破它的头。在其他函数的帮助下,终于统计出来了。

= sum product(& # 8211;ISNUMBER(FIND(G2,$A$2:$A$10)))

这个公式相当复杂。我们一步步来分析吧。

步骤01首先看最里面的FIND函数,FIND函数的语法:

=FIND(搜索的字符,搜索的位置)

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

=查找(& # 8220;风& # 8221;,”风很清& # 8221;)

=查找(& # 8220;风& # 8221;,”东方不败& # 8221;)

COUNTIF与SUMPRODUCT函数过招!

在第一个公式中,结果返回1,因为字符“wind”位于字符串“wind is clear”的第一个位置。在第二个公式中,因为字符“wind”不在字符串“东方不败”中,所以结果返回一个错误值。

步骤02熟悉FIND函数的基本应用后,我们在单元格I2中输入公式:

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

COUNTIF与SUMPRODUCT函数过招!

我们知道,在“A2:A10”地区,有两个“冯”。按道理可以找到“丰”这个字符,所以应该返回数字,但是却返回了错误的值。为什么?

FIND函数的第二个参数是一个区域,所以返回的结果是一些数据。如果多个数据放在一个单元格中,当然会有错误。这时候就需要用到一个神器:独孤九剑,也就是F9键。选择公式所在的单元格,单击编辑栏,然后按F9。

COUNTIF与SUMPRODUCT函数过招!

步骤03带红框的数字代表值班经理的值班时间。那么你是怎么统计数字的呢?可以使用ISNUMBER函数,如果是数字则返回TRUE,否则返回FALSE。

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

COUNTIF与SUMPRODUCT函数过招!

步骤04结合SUMPRODUCT函数,结果就出来了,公式上面已经给出了。

第三场比赛,虽然SUMPRODUCT函数最终完成了任务,但是评委的眼睛是雪亮的。这一次,评委们投票支持COUNTIF函数。

比赛结果不重要,重要的是什么时候用什么功能。怎么可能简单?作为本次比赛的吃瓜观众,你怎么说?