Excel两个以上条件统计CountIfs函数的使用方法，含与Sum组合统计满足And和Or条件的个数

在 Excel 中，CountIfs函数用于同时满足两个以上条件的个数统计，CountIf函数用于满足一个条件的个数统计。CountIfs函数参数由条件区域/条件对组成，至少要有一个条件区域/条件对，最多只能有 127 条件区域/条件对。

大多情况下，CountIfs函数本身就可以完成满足多个条件的统计，但它自身无法完成“或关系”（Or 条件）的统计，如果要实现此功能，需要与Sum函数组合；另外，如果要实现区域不固定的个数统计，需要用 CountIfs + OffSet。

一、Excel CountIfs函数语法

1、表达式：COUNTIFS(Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], …)

中文表达式：COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2], …)

2、说明：

A、一个条件区域和一个条件组成一个CountIfs函数的条件区域/条件对，至少要有一个条件区域/条件对，最多只能有 127 个条件区域/条件对；每个条件之间是“与”的关系，假如有两个条件，则要求同时满足两个条件。

B、条件可以是数字、文本（如："员工"）、单元格引用、表达式（如：">=100"），且条件中可以有函数(如："<="&Average(A2:A8)）。

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

D、如果同时引用多个条件区域，每个条件区域的行数和列数必须相同，但它们不必相邻，否则返回值错误 #VALUE!。

C、如果条件是对空单元格的引用，CountIfs函数将返回 0。

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

（一）条件为文本且只有一个条件区域/条件对的实例

1、假如要统计销售业绩表中业绩评定为优异的员工个数。双击 D9 单元格，把公式 =COUNTIFS(D2:D8,"优异") 复制到 D9，按回车，返回统计结果 2；操作过程步骤，如图1所示：

图1

2、公式说明：

在 =COUNTIFS(D2:D8,"优异") 中，D2:D8 为条件区域，"优异"为条件，意思是：在 D2 至 D8 中统计“业绩评定为优异”的个数；一般情况下，一个条件常常用CountIf函数，两个以上条件用CountIfs函数。

（二）条件为表达式和有函数且有两个条件区域/条件对的实例

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

图2

2、公式 =COUNTIFS(C2:C7,">3",D2:D7,">="&AVERAGE(D2:D7)) 说明：

第一个条件区域/条件为 C2:C7,">3"，用于在 C2:C7 找出所有价格大于 3 的数值；第二个条件区域/条件对为 D2:D7,">="&AVERAGE(D2:D7)，用于在 D2:D7 中找出所有销量大于等于平均值的数值，AVERAGE(D2:D7) 用于求销量的平均值，连接运算符 & 用于把 ">=" 与平均值连接起来。最后筛选出既满足第一个条件又满足第二个条件的数值作为统计结果。

（三）条件是对空单元格的引用，CountIfs函数将返回 0 的实例

双击 C9 单元格，把公式 =COUNTIFS(C2:C8,A8) 复制到 C9，按回车，返回统计结果 0；选中 C9，按住 Alt，分别按一次 M 和 V，打开“公式求值”窗口，单击“求值”或按回车，A8 返回 0，说明条件为空单元格时，CountIfs函数将它的值视为 0；操作过程步骤，如图3所示：

图3

（四）条件为空与不为空的实例

1、假如要统计服装尺码表中，尺码 S 不为空且 L 为空的个数。双击 B11 单元格，把公式 =COUNTIFS(B2:B10,"<>",D2:D10,"") 复制到B11，按回车，返回统计结果 2；操作过程步骤，如图4所示：

图4

2、公式=COUNTIFS(B2:B10,"<>",D2:D10,"") 说明：

公式中第一个条件 "<>" 表示不为空，也可以表示为 "<>"&""；第二个条件 "" 表示为空。

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

1、假如要统计服装名称同时满足以“粉红”开头、以“衬衫”结尾且仅由四个字组成的个数。双击 B9 单元格，把公式 =COUNTIFS(B2:B8,"粉红*",B2:B8,"*衬衫",B2:B8,"????") 复制到 B9，按回车，返回统计结果 1；操作过程步骤，如图5所示：

图5

2、公式 =COUNTIFS(B2:B8,"粉红*",B2:B8,"*衬衫",B2:B8,"????") 说明：

公式中的三个条件区域都为 B2:B8；条件 "粉红*" 意思是以“粉红”开头，* 表示任意一个或多个字符；条件 "*衬衫" 表示以任意一个或多个字符开头但要以“衬衫”结尾；条件 "????" 表示仅由四个字符组成。

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

（一）CountIf + OffSet函数组合返回错误的原因

1、双击 A10 单元格，把公式 =COUNTIFS(OFFSET($A$1,1,1,6,1),">600",OFFSET($A$1,2,2,7,1),">600") 复制到 A10，按回车，返回值错误 #VALUE!，选中 A10，按住 Alt，依次按一次 M 和 V，打开“公式求值”窗口，按回车“求值”，直到公式变为 COUNTIFS($B$2:$B$7,">600",$C$3:$C$9,">600")；关闭“公式求值”窗口，双击 B10，把公式改为 =COUNTIFS(OFFSET($A$1,1,1,8,1),">600",OFFSET($A$1,1,2,8,1),">600")，按回车，返回统计结果 3，同样方法打开“公式求值”窗口，按回车求值直到公式变为 COUNTIFS($B$2:$B$9,">600",$C$2:$C$9,">600")；操作过程步骤，如图6所示：

图6

2、公式说明：

A、公式中 OFFSET($A$1,1,1,6,1) 用于返回 A1 下 1 行 1 列且高度为 6、宽度为 1 的单元格引用，即返回对 B2:B7 的引用；$A$1 表示对 A1 的绝对引用，无论往下拖还是往右拖，A1 始终不会变。

B、OFFSET($A$1,2,2,7,1) 用于返回 A1 下 2 行 2 列且高度为 7、宽度为 1 的单元格引用，即返回对 C3:C9 的引用。

C、则公式变为 =COUNTIFS($B$2:$B$7,">600",$C$3:$C$9,">600")，条件区域 $B$2:$B$7 与 $C$3:$C$9 的行数不相同，而CountIfs函数要求条件区域的行数和列数相同，因此公式返回值错误 #VALUE!。

D、当把公式改为 =COUNTIFS(OFFSET($A$1,1,1,8,1),">600",OFFSET($A$1,1,2,8,1),">600") 后，两个 OffSet 返回的行数和列数相同（即都为 $B$2:$B$9），因此能返回正确的统计结果。

（二）Sum + CountIfs函数组合实现同时满足 And 和 Or 条件的统计

1、假如要统计行政部和财务部的员工总数。双击 B9 单元格，把公式 =SUM(COUNTIFS(C2:C8,"员工",B2:B8,{"行政部","财务部"})) 复制到 B9，按回车，返回统计结果 3，操作过程步骤，如图7所示：

图7

2、公式 =SUM(COUNTIFS(C2:C8,"员工",B2:B8,{"行政部","财务部"})) 说明：

A、公式第二个条件为数组 {"行政部","财务部"}，意思是在 B2:B8 中统计“行政部和财务部”的员工；执行时，先统计出“行政部”的员工个数（结果为 2），再统计出“财务部”的员工个数（结果为 1），CountIfs 最后返回数组 {2,1}。

B、则公式变为 =SUM({2,1})，最后用 Sum 对数组求和，结果为 3；如果不用 Sum，只返回满足数组 {"行政部","财务部"} 中第一个条件的统计结果。