Excel满足特定条件的单元格进行求和或汇总

Excel满足特定条件的单元格进行求和或汇总如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF 工作表函数。

关于SUMIF 函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIR 的应用。

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000 元以上的员工数。

语法形式为COUNTIF(range,criteria)其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、"32"、">32"、"apples"。

1、成绩表这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是：每位

学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8 所示

根据这一规则，我们在优秀门数中写公式(以单元格B13为例)：

=COUNTIF(B4:B10,">90")

语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90 的单元格。

在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。

2、销售业绩表

销售业绩表可能是综合运用|F、SUMIF COUNTIF非常典型的示例。比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。

原始数据表如图9 所示（原始数据是以流水单形式列出的，即按订单号排列）图9 原始数据表

按销售人员汇总表如图10 所示

如图10 所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。

（1）订单数--用COUNTIF计算销售人员的订单数。以销售人员ANNIE的订单数公式为例。公式：

=COUNTIF（$C$2:$C$13,A17）

语法解释为计算单元格A17（即销售人员ANNIE在"销售人员"清单$C$2:$C$13的范围内（即图9所示的原始数据表）出现的次数。

这个出现的次数即可认为是该销售人员ANNIE的订单数。

（2）订单总额--用SUMIF汇总每个销售人员的销售额。

以销售人员ANNIE的订单总额公式为例。公式：

=SUMIF（$C$2:$C$13,A17,$B$2:$B$13）

此公式在"销售人员"清单$C$2:$C$13中检查单元格A17中的文本（即销售人员ANNIE），然后计算"订单金额咧（$B$2:$B$13）中相应量的和。

这个相应量的和就是销售人员ANNIE的订单总额。

（3）销售奖金--用IF根据订单总额决定每次销售应获得的奖金。

假定公司的销售奖金规则为当订单总额超过 5 万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式

为：

=IF(C17<50000,10%,15%)*C17

如果订单总额小于50000 则奖金为10%；如果订单总额大于等于50000，则奖金为15%。

图10 销售人员汇总表

图8

SUMIF

用途：根据指定条件对若干单元格、区域或引用求和。

语法：SUMIF(range criteria，sum_range)参数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。

实例：某单位统计工资报表中职称为“中级”的员工工资总额。假设工资总额存放在工作表的F列，员工职称存放在工作表B列。则公式为

“ =SUMIF(B1:B1000'中级”，F1:F1000)，”其中“ B1:B100为提供逻辑判断依据的单元格区域，"中级"为判断条件，就是仅仅统计B1:B1000区域中职称为中级” 的单元格，

F1:F1000为实际求和的单元格区域。

例如：sumif(A1:A20,"a",B1:B20)

意思就是：在A1到A20这个区域中，凡是"a"的就把它的数量求和。如果是不同一个工作表，只要在区域的前面加上工作表的名称就行了，如

sumif(sheet1!A1:A20,"a",sheet1!B1:B20)

但要注意的一点就是在判断条件时，条件一定要与字段名相同，就算差一个点或一个空格，公式都无法判断，所以条件与字段名一定要一致

COUNTIF函数(计数求和)

COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条件才进行统计计算。比如，从员工信息表中，计算出有多少人的年龄大于35 岁。下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表，每名销售人员累计做了多少“销售订单个数”呢？

COUNTIF正常工作需要两个参数一一件区域（本例为左侧表中销售人员” 一列）和计数条件（本例为右侧表中的人员姓名）。要计算第一位销售人员的订单数”，考试大提示很简单，输入函数公式“二COUNTIF（$C$2:$C$16,E2）” 可。

二：SUMIF函数（条件求和）

SUM函数的作用是对数据求和，而SUMIF对它进行了引伸和拓展，比如计算“金额”在 1 元以上的数据总和、按照人员或产品分类计算数据总和等等。它有 3 个参数，分别是条件区域、判断条件、实际的求和区域（如果它与“条件区域”是一个区域，就可省略）。

在上例中，计算每位销售人员”的订单总金额，就要使用SUMIF函数来协助了。如果要计算每个人的销售订单总金额，把左侧表的“销售人员”一列当作“条件区域”，把右侧表的每个名单当作求和“条件”，把左侧表的每笔“订单金额” 当作实际求和区域”，在G2单元格中输入数据计算公式

“二SUMIF（$C$2:$C$16,E2,$B$2:$B$16第T名销售人员的订单总额”就瞬间产生了。

小提示：

在本例的COUNTIF?数和SUMIF函数中，由于销售人员”区域与订单总额”区域都是固定的，所以在函数中引用这两列地址时，要使用“绝对地址”，也就是在地址前添加“ $符号。三：IF函数

逻辑高手IF函数是一个条件函数，它可以通过设置的条件进行逻辑判断。如果在刚才的数据汇总表中再添加一列“销售奖金”数据，“销售奖金”发放的方法是：如果某个人的订单总额大于Y 1500,000,那么奖金”数用总额X5%否则奖金”就用总额X3%所以，第1位销售人员销售奖金”的计算公式应为

“ =IF(G2>1500000,G2*0.05,G2*0.03。) ”

结合刚才的知识，推测一下这个公式中3个参数的涵义吧。“G2> 1 500000” 是IF函数的判断条件，“G2*0.05是条件成立的操作，“G2*0.03是条件不成立的操作。

点睛：

*数据的分类汇总是ExceI最常见的应用。分类汇总的方法很多，包括使用函数和公式。

设置的条件可看作是分类的依据，用“COUNT函数”进行分类计数汇总，用

“SUMI函数”进行分类求和汇总，非常方便。*IF函数的作用是根据判断条件的真假，自动进行分支操作。比如根据身份证号码的奇偶来判断性别，根据绩效数据来填写成绩等等。在实际应用时，一个非常实用的应用是：把IF函数的

真”或假”参数嵌套成另一个IF函数，实现多种分支操作。如2个IF函数的嵌套可写成“=(条件，真，IF(条件，真，假))”这样一来就可以实现3个分支判断了(在Excel中，最多

是7层函数嵌套)。

*重复数据的筛选问题常常让我们感到非常棘手。COUNTIF函数除了可以实现分类计数汇总外，它和IF函数配合，还可以实现对重复数据的标识与筛选，从而将 1 列中的重复数据删除。使用了公式“=IF(COUNTIF($C$1:C1,C1)>1,”1”,”0”) 为重复数据的后面添加了标识“1，”为不重复数的后面添加了标识“0。”在公式中，COUNTIF是条件计数，可利用它统计出从这列的第1个数据起，某个数出现了几次，再用IF函数判断，若出现2次以上就添加“ 1,若出现1次则就添加

“0”最后用Excel中的自动筛选功能，将所有标识为“1的数据行删除，就能把重复数据统统清理掉了。