Sumif函数是按给定条件对指定单元格求和。
首先,我们通过一个简单的数据来认识Sumif函数怎么使用。下图是一个工作表源数据。
使用SUMIF函数求出A列大于15的单元格,与它对应的B列单元格求和。
在求值单元格中输入:=SUMIF(A1:A4,">15",B1:B4),得到结果为900。
公式的意思就是因为A2、A3、A4中的数据满足条件">15",所以就将相对应的B2、B3、B4中的数据进行求和计算。
SUMIF函数的语法为:SUMIF(range,criteria,sum_range)
其中的参数介绍如下:
第一,range:是要根据条件计算的单元格区域。每个区域中的单元格都必须是数字和名称、数组和包含数字的引用。空值和文本值将被忽略。
第二,Criteria:为确定对哪些单元格相加的条件,其形式可以为数字、表达式或文本。例如,条件可以表示为65、"65"、">65" 或 "apples"。
第三,Sum_range:为要相加的实际单元格(如果区域内的相关单元格符合条件)。如果省略sum_range,则当区域中的单元格符合条件时,它们既按条件计算,也执行相加。
Sumif函数支持通配符计算。可以在条件中使用通配符、问号(?) 和星号 (*)。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在该字符前键入波形符(~)。
第二部分:sumif函数和COUNTIF函数的应用案例分析
第一题:SUMIF函数是用于求和,COUNTIF函数是用于计数。分别用这两个函数来求和和计数应用。
1.下面的工作表C列为源数据。求出大于50小于100的个数。
公式为:=COUNTIF($C2:$C10,">"&E2)-COUNTIF(C2:C10,">"&F2),得到结果为4。
另外也可以嵌套SUM函数来计算,公式为:=SUM(COUNTIF(C2:C10,{">50",">=100"})*{1,-1})。
2.求出大于50小于100的和。
求和,我们只需要将上面公式中的COUNTIF更换为sumif函数即可。
公式为:=SUMIF($C2:$C10,">"&E2)-SUMIF(C2:C10,">"&F2),得出结果为:274。
或者使用数组公式:=SUM(COUNTIF(C2:C10,{">50",">=100"})*{1,-1}),三键结束。
第二题:下图是一个源数据,E33,F33是计算条件,完成下面几题。
1.求型号为A04和A03单元格值对应的数量的和。
可以套用sumif(区域,条件,求和区域),得出公式为:=SUMIF(C22:G30,E33,D22:H30)+SUMIF(C22:G30,F33,D22:H30),得到结果为:745。
或者使用数组公式:=SUM(SUMIF(C22:G30,E33:F33,D22)),三键结束。
2.求型号为A04和A03单元格值对应的数量有几个。
求个数与求和基本原理一样,只是函数不同。因此公式为:=COUNTIF(C22:H30,E33)+COUNTIF(C22:H30,F33)
或者使用数组公式=SUM(COUNTIF(C22:G30,E33:F33)),三键结束。
3.求型号以A开始的但不包含A03的产品数量的和。
SUMIF与COUNTIF函数都支持通配符,可以在条件中使用通配符、问号 (?) 和星号(*)。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在该字符前键入波形符 (~)。
公式分析:
用SUMIF这个函数就可以求出以A开始的总数量。公式为:=SUMIF(C22:G30,F33,D22),然后用这个值减去A03的值就是除A03以外的值。
公式为:=SUMIF(C22:G30,"A*",D22)-SUMIF(C22:G30,F33,D22),得到结果为:998。
COUNTIF函数是一个比较简单的函数,但真正使用好了也是非常实用的。countif函数的功能就是用来计算个数,表示计算区域中满足给定条件的单元格的个数。
第二部分:COUNTIF函数的用法
countif函数的语法为:COUNTIF(range,criteria),其中Range参数:是为需要计算其中满足条件的单元格数目的单元格区域,Criteria参数:为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。
下图是一个电脑配件销售表,本次分享我们要完成下面几个知识点的学习。
第一,统计上图数据源中“数量”大于30的个数以及“单价”小于100的个数。
先看看“数量”大于30的个数,套用COUNTIF函数的语法:countif(区域,条件),得出公式:=COUNTIF(D6:D35,">30"),结果为:8个。
说明:在公式中的> <= 都要用" ",而引用单元格就不需要 " "。
此题另外还可以使用数组公式:=COUNT(IF(D6:D35>30,1)),按下【Ctrl】+【Shift】+【Enter】三键,完成数组的输入。
也可以使用这样的公式:=COUNTIF(D6:D35,">"&D7)。“&”此符号就是文本粘贴符,后面的是单元格地址,意思就是连接D7单元格的内容。大家可以在工作表中查看D7单元格的内容就是30。如果大于后面没有函数,就没有比对的目标,所以要用&D7。
有一种查看公式中部分内容的方法,就是按下F9键,俗称“抹黑”。F9键在学习函数与公式中,对我们来说,有很大的帮助作用,帮助我们理解公式。
在本公式中,如果在公式编辑栏选中后面的">"&D7,按下F9键,可以看出变为了">30",就和设计的第一种公式一样的,即:=COUNTIF(D6:D35,">30")
当然解决一个问题,设计的公式也许不只一种,只要大家根据自己的理解,灵活使用就可以了,得出的答案都是一样的。
用同样方法可以得出“单价”小于100的个数,公式为:=COUNTIF(D6:D35,"<100"),得到正确答案为:30。
第二,统计上图数据源中,“营业部”中含“河”字的个数,以及在“商品”这列中是否有键盘。
要求解出答案,首先,需要领会COUNTIF函数中通配符的使用规则,COUNTIF 函数是支持通配符的,在COUNTIF函数中可以引用通配符。其中通配符?号代表单个字符,*号代表多个字符。
因此,统计“营业部”中含“河”字的个数,公式为:=COUNTIF(A6:A35,"*河"),得到正确答案为:12。
另外一种公式写法:=COUNTIF(A6:A35,"*河*"),也可以得到答案。
其实,通配符*号和find函数差不多,因此还可以这样设计公式:=COUNT(FIND("河",A6:A35)),然后按下【Ctrl】+【Shift】+【Enter】三键,完成数组的输入。
在“商品”这列中是否有键盘,可以这样设计公式:=IF(COUNTIF(B6:B35,"键盘"),"是","否")。
第三,一次行列出营业部中“天河”、“黄埔河”、“黄埔”、“越秀”、“荔湾”、“超秀”出现的次数。
对于一次性统计多单元格出现次数,可以使用数组公式。方法是:先选中L19:L24单元格区域,然后在编辑栏中输入公式=COUNTIF(A6:A35,K19:K24),然后按下【Ctrl】+【Shift】+【Enter】三键,完成数组的输入。得到答案为:9、3、4、7、6、1次。
另外此题,也可以使用普通公式:=COUNTIF($A$6:$A$35,K19),然后往下拉,复制公式即可。
第四,统计“销售日期”列下面的日期总共出现次数。
计算数据源中日期总共出现的次数,也需要用到数组公式,这个公式在网上也是个经典例子。公式为:=SUM(1/COUNTIF(C6:C35,C6:C35)),然后三键结束。结果为:30。
公式分析:公式中,COUNTIF(C6:C35,C6:C35)的结果为1,大家可以在上面公式中把这部分抹黑,得到结果为1。就是统计C6:C35每一个单元格内容出现的次数。
在将公式中1/COUNTIF(C6:C35,C6:C35)部分抹黑,执行公式,得到数组形式的{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1},一共有30个1。1/就是每一个数值占1的百分比,来相加。抹黑查看完公式结果,可以按Esc键返回公式。最后再用SUM函数合计。
此题,还可以有下面两种公式设计,都可以实现结果。
第一:=SUM(--(MATCH(C6:C35,C6:C35,)=ROW(C6:C35)-5)),三键结束。
第二,设计普通公式:=COUNT(1/FREQUENCY(C6:C35,C6:C35))。
有网友说到此题也可以使用公式:=COUNTIF(C6:C35,"<>0")。这个公式实质有点问题,只是算C6:C35区域的数据,在本题中,虽然答案是一样的,是因为日期不重复,如果源数据中有两个相同的日期,结果就不对了。大家可以更改一下源数据里面的日期做个小试验。
第五,统计“商品”列中不重复的有哪几个?
此题可以理解为提取不重复值,最简单的方法是使用高级筛选,操作步骤:单击菜单“数据”——“筛选”——“高级筛选”,在“方式”下面选中:将筛选结果复制到其他位置,列表区域为:$B$6:$B$35,复制到:K32,勾选“选择不重复的记录”。
说明:高级筛选的不好之处,就是工作表中的源数据更新后,筛选出来的结果是不会更新的。
本题也可以使用公式来求解。下面设计的三种公式均能实现最终结果。
公式一:=INDEX($B$6:$B$35,MATCH(,COUNTIF($L$31:L31,$B$6:$B$35),)),然后三键结束公式输入,下拉。
公式二:=INDEX($B$6:$B$35,SMALL(IF(MATCH($B$6:$B$35,$B$6:$B$35,)=ROW($B$6:$B$35)-5,ROW($B$6:$B$35)-5,1000),ROW(A1))),然后三键结束公式输入,下拉。
公式三:=LOOKUP(1,0/(NOT(COUNTIF($K$31:K31,$B$6:$B$35))),$B$6:$B$35),然后往下拉,复制公式,直到出现#N/A错误值。本题的答案是:硬盘,显示器,鼠标。