Excel CountIf函数的使用方法，包含与If、Sum组合实现统计不重复与多条件实例

在 Excel 中，CountIf函数用于统计指定范围内满足某个条件的单元格个数；它只有两个参数，一个是统计范围，另一个是条件，其中条件不能超过 255 个字符，如果超过需要分开。条件中仅为 *、?、>、< 等，可以完成一些特殊的统计。

CountIf函数如果要实现多条件的的统计需要与 If、OffSet、Sum函数组合，并且有时条件需用数组，例如：Sum + If + CountIf函数组合实现统计不为空且不包含重复值的个数，Sum + CountIf + 数组条件实现同一范围内满足两个条件的统计。

一、Excel CountIf函数语法

1、表达式：COUNTIF(Range, Criteria)

中文表达式：COUNTIF(统计区域, 条件)

2、说明：

A、Range 可以是数组或单元格的引用。

B、条件可以是单个数字或文本，也可以为表达式或函数；但条件中不能超过 255 个字符，否则会返回错误；如果条件超过 255 个字符，可以把它们拆分为多个长字符串，然后用 Concatenate 函数或连接运算符 & 把它们连接起来，例如：”长字符中1……”&”长字符串2……”。另外，条件忽略字母大小写。

C、在条件中，可以使用通配符问号（?）和星号（*），问号表示任意一个字符，星号表示任意一个或一串字符；如果要查找问号或星号，需要在它们前面加转义字符~；如：查找问号，表达式应该这样写 ~?；查找星号，表达式应该这样写 ~*。

D、统计文本个数时，不能包含前导与尾部空格，也不能出现单双引号不一致和非打印字符；否则，CountIf函数可能返回不可预期的值。统计文本前或后有空格时，可以用Trim函数把它们去掉；有非打印字符时，可以用Clean函数把它们去掉 。

二、Excel CountIf函数的使用方法及实例

（一）条件为文本和统计空值的实例

1、双击 D2 单元格，把公式 =COUNTIF(C1:C11,”经理”) 复制到 D2，按回车，返回统计结果 3；双击 D2，把双引号内的“经理”去掉，按回车，返回统计结果 1；操作过程步骤，如图1所示：

图1

2、公式说明：

在 =COUNTIF(C1:C11,”经理”) 中，C1:C11 为统计区域，”经理”为条件，意思是：在 C1 至 C11 中统计“经理”的个数；当条件为 “” 时，统计空值的个数，C1:C11 中只有一个空单元格，因此统计结果为 1。

（二）条件为表达式的实例

1、假如要统计水果销量大于等于 1000 的个数。双击 D8 单元格，把公式 =COUNTIF(D2:D7,”>=1000″) 复制到 D8，按回车，返回统计结果 5；操作过程步骤，如图2所示：

图2

2、公式 =COUNTIF(D2:D7,”>=1000″) 中的条件为 “>=1000″，意思是：在 D2:D7 中统计数值大于等于 1000 的个数；另外，”>=1000″ 还可以这样写 “>”&”1000″，即把大于号与数字分开写，再用连接运算符把它们连接起来。

（三）条件中有函数的实例

1、假如要统计水果销量大于平均值的个数。双击 D8 单元格，把公式 =COUNTIF(D2:D7,”>”&AVERAGE(D2:D7)) 复制到 D8，按回车，返回统计结果 3；操作过程步骤，如图3所示：

图3

2、公式 =COUNTIF(D2:D7,”>”&AVERAGE(D2:D7)) 中的条件为 “>”&AVERAGE(D2:D7)，条件中的 AVERAGE(D2:D7) 用于求 D2:D7 的平均值（结果为 1790.5），然后用 & 把大于号跟它连起来组成条件，即 “>1790.5″。

（四）条件中有通配符 ? 和 * 的实例

1、假如要统计姓李的员工个数。双击 D2 单元格，把公式 =COUNTIF(A2:A10,”李??”) 复制到 D2，按回车，返回统计结果 2；双击 D2，把公式中的 ?? 改为 *，按回车，也返回统计结果 2；操作过程步骤，如图4所示：

图4

2、公式 =COUNTIF(A2:A10,”李??”) 中的条件为 “李??”，意思是“以李字开头且李字后仅有两个字”；当把条件改为”李*”后，意思是“以李字开头即可”，李字后可以有一个或多个任意字符。条件中有通配符 ? 和 * 的情况在前面的篇章中已介绍多次，如果想了解更多的查找方式，可以参考《Excel HLookUp函数的使用方法，包含首行需排序、返回#N/A及与Match组合/非首行实例》一文。

3、条件为 * 和 ?* 的实例

A、条件为 * 的实例。双击 E10 单元格，把公式 =COUNTIF(E1:E9,”*”) 复制到 E10，按回车，返回统计结果 2；双击 E9，把返回空（””）的公式去掉，按回车，统计结果变为 1；再把 E1 中的文字删除，统计结果变为 0，演示如图5所示：

图5

B、条件为 ?* 的实例。双击 E10 单元格，把公式 =COUNTIF(E1:E9,”?*”) 复制到 E10，按回车，返回统计结果 1；双击 E9，把 “” 改为” “，统计结果变为 2；再把 E1 中的文字删除，统计结果变为 1，演示如图6所示：

图6

* 和 ?* 的区别：* 可以是一个或多字符（包括空值””），?* 至少要有两个字或空值””，演示中已经说明这两个问题。

（五）几个特殊的实例

1、双击 B11 单元格，把公式 =COUNTIF(B2:B10,”>=!”) 复制到 B11，按回车，返回统计结果 7；双击 B10，它是一个空单元格，再双击 B9，里面有一个公式，双击 B7，按空格键在“财务部”前加一个空格，按回车，统计结果变为 7；把公式 =COUNTIF(B2:B10,”><“) 复制到 B12，按回车，返回统计结果 6，双击 B7，把“财务部”前的空格删除，B11 和 B12 中的统计结果都变为 7；双击 B13，把公式 =COUNTIF(B2:B10,”>”””) 复制到 B13，按回车，返回统计结果 7，双击 B7，再次在“财务部”前加一个空格，B11、B12 和 B13 的统计结果都变为 7；双击 C11，把公式 =COUNTIF(B2:B10,”<>”) 复制到 C11，按回车，返回统计结果 8；操作过程步骤，如图7所示：

图7

2、公式说明：

公式 =COUNTIF(B2:B10,”>=!”)、 =COUNTIF(B2:B10,”><“) 和 =COUNTIF(B2:B10,”>”””) 的统计结果一样，它们都不包含有公式的单元格、空单元格和文字前有空格的单元格；而公式 =COUNTIF(B2:B10,”<>”) 包含有公式的单元格，不包含空单元格。

三、Excel CountIf函数的扩展使用实例

（一）Sum + If + CountIf函数组合实现统计不为空且不包含重复值的个数

1、双击 B9 单元格，把公式 =SUM(IF(B2:B8<>””,1/COUNTIF(B2:B8,B2:B8))) 复制到 B9，按 Ctrl + Shift + 回车，返回统计结果 3，操作过程步骤，如图8所示：

图8

2、公式 =SUM(IF(B2:B8<>””,1/COUNTIF(B2:B8,B2:B8))) 说明：

A、B2:B8 用于以数组形式返回 B2 到 B8 中的所有值，即 {“财务部”;”销售部”;”行政部”;”销售部”;”行政部”;”财务部”;”行政部”}。

B、COUNTIF(B2:B8,B2:B8) 执行时，第一次从 B2:B8 中取出 B2（即“财务部”），然后统计 B2 在 B2:B8 中的个数，结果为 2；第二次统计 B3 在 B2:B8 中的个数，结果为 2；其它的以此类推，最后返回 {2;2;3;2;3;2;3}。

C、1/COUNTIF(B2:B8,B2:B8) 变为 1/{2;2;3;2;3;2;3}，接着用 1 除以数组中的每一个元素，结果返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}；这一步的作用是把重复值的总数看为 1，例如重复次为 2 的，每个 1/2 等于 0.5，进一步计算再把两个 0.5 加起来，恰好是 1，即去掉一个重复项。

D、B2:B8<>”” 意思是：每次从 B2:B8 中取出一个元素，如果不为空，返回 True，否则返回 False；最后返回{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}。

E、则公式变为 =SUM(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}))，接着从 If 的条件数组中取出第一个元素 TRUE，由于为真，所以返回第二个数组中对应的元素 0.5；直到取完条件数组中的每个，最后返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}；则公式变为 =SUM({0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333})，最后用 Sum 对数组中的每个元素求和，结果为 3。

（二）OffSet + CountIf函数组合使用实现分段统计

OffSet + CountIf 实现分段统计请查看《Excel OffSet函数的使用方法，含与Sum、Match、CountIf、If、Or、Row组合实现动态求和、一个重复多个、分段统计和断码检查》一文。

（三）CountIf函数多条件统计

1、假如要统计销售额在 5 万到 8 万的销售员个数。双击 C9 单元格，把公式 =SUM(COUNTIF(C2:C8,{“>=50000″,”>80000″})*{1,-1}) 复制到 C9，按回车，返回统计结果 3，操作过程步骤，如图9所示：

图9

2、公式 =SUM(COUNTIF(C2:C8,{“>=50000″,”>80000″})*{1,-1}) 说明：

A、C2:C8 为 CountIf 的统计范围，{“>=50000″,”>80000″} 为条件，该条件为数组，共包含两个条件，第一个条件为 “>=50000″，第二个条件为 “>80000″，意思是：先在 C2:C8 中统计出大于等于 50000 的个数，结果为 4；再统计出大于 80000 的个数，结果为 1。

B、则公式变为 =SUM({4,1}*{1,-1})，接着，把两个数组的对应元素相乘，公式变为 =SUM({4,-1})，最后对数组求和，结果为 3。这个方法的关键为：先统计出所有大于等于 5 万的个数，再统计出大于 8 万的个数，然后用前者减后者。

3、统计其它范围的公式分别为：

A、大于 50000 且小于 80000：=SUM(COUNTIF(C2:C8,{“>50000″,”>=80000″})*{1,-1})

B、大于等于 50000 且小于 80000：=SUM(COUNTIF(C2:C8,{“>=50000″,”>=80000″})*{1,-1}) 或 =SUM(COUNTIF(C2:C8,”>=”&{50000,80000})*{1,-1})

C、大于 50000 且小于等于 80000：=SUM(COUNTIF(C2:C8,{“>50000″,”>80000″})*{1,-1}) 或 =COUNTIF(C2:C8,”>50000″）- COUNTIF(C2:C8,”>80000″）