（原创）多工作表两（多）条件计数 - COUNTIFS函数

dengjq2000

   论坛里有很多针对多工作表单条件进行计数要求通过公式来实现的求助主题，该方面的主题通常可以借助公式：SUMPRODUCT+COUNTIF+INDIRECT 或：数组公式：SUM+COUNTIF+INDIRECT 来很好的实现，也可以通过：数据透视表 - 多重合并计算数据区域 来实现。

    可是对于多工作表双条件或更多条件计数时，就不能借助上面两种方式来实现，一般需借助VBA或SQL语句来实现。但VBA与SQL语句对于大部分E菜鸟们还是有点高深。

现在介绍通过EXCEL 2007新增加的COUNTIFS 函数来实现。

    具体下：

    三张原始数据源工作表，分别为：数学、物理、化学。

工作表：数学 部分数据为：

学校	班级	姓名	竞赛成绩
十一中	0701	王敏欣	98
十一中	0701	张旭	90
十一中	0701	刘政	92
十二中	0701	高璐	89
十二中	0701	戴娇	95
十二中	0702	杨越	94
十二中	0702	杨娜	85
十三中	0701	李超	88
十三中	0701	王水雯	89
十三中	0701	吴明周	90
十三中	0701	霍文强	93
十三中	0702	刘俊琪	96
十三中	0702	胡温红	87

工作表：物理 部分数据为：

学校	班级	姓名	竞赛成绩
十一中	0701	王敏欣	97
十一中	0701	张旭	89
十一中	0701	窦浩真	91
十二中	0701	张娟	76
十二中	0701	高璐	88
十二中	0701	戴娇	94
十二中	0702	杨越	93
十二中	0702	杨娜	84
十二中	0702	马琴	86
十二中	0702	高磊	94
十二中	0702	张世豪	90
十二中	0703	邬轶芳	95
十二中	0703	郭智	78
十三中	0701	梁欣悦	87
十三中	0701	刘荣	89

工作表：化学 部分数据为：

学校	班级	姓名	竞赛成绩
十一中	0706	张恒	100
十一中	0706	李晴	100
十三中	0706	王娜大	100
十一中	0705	姚璐	99
十二中	0703	薄宜书	98
十一中	0702	许文强	97
十一中	0704	隗高鸣	97
十二中	0705	郝秀玲	97
十三中	0702	刘俊琪	97
十三中	0704	郭娜	97
十一中	0705	高凯	96
十一中	0701	孙悦	95

如果只针对单条件：“学校”进行对多工作表参考人数统计时，这可以用公式（也是论坛已有的解法）：

公式为:

1. =SUMPRODUCT(COUNTIF(INDIRECT({"数学";"化学";"物理"}&"!A:A"),A2))

复制代码

或公式：

1. =SUM(COUNTIF(INDIRECT({"数学";"化学";"物理"}&"!A:A"),A2))

复制代码

同时也可以通过：数据透视表 - 多重合并计算数据区域：具体见可以视操作（在EXCEL 2007里可以通过依次按：ALT键、D键、P键，即可把 数据透视表和数据透视图向导 调出来）

   可是对于双条件：“学校”及“班级”进行进行对多工作表参考人数统计时，借助 COUNTIF 函数是实现不了，一般需借助VBA（暂没列出）或SQL语句（具体见附件工作表：“年级班级双条件汇总SQL”的效果）来实现。但VBA与SQL语句对于大部分E菜鸟们还是有点高深。

SQL语句为：

1. 
   
2. select 学校,班级,count(姓名) as 参考人数 from
   
3. (select * from [数学$A2:C100] union all select * from [物理$A2:C100] union all select * from [化学$A2:C100])
   
4. group by 学校,班级

复制代码

    幸运的是，在EXCEL 2007版本新增了的 COUNTIFS 函数可以帮我们解决这个问题：

公式：

1. =SUMPRODUCT(COUNTIFS(INDIRECT({"数学";"化学";"物理"}&"!A:A"),A2,INDIRECT({"数学";"化学";"物理"}&"!B:B"),B2))

复制代码

或公式：

1. =SUM(COUNTIFS(INDIRECT({"数学";"化学";"物理"}&"!A:A"),A2,INDIRECT({"数学";"化学";"物理"}&"!B:B"),B2))

复制代码

继续追溯：

可想而知，如果三个条件或更多的条件进行多工作表计数统计时，只需在条件区域增加相应的条件即可（此不再举例）。

该贴已经同步到 dengjq2000的微博

zhaoyafei19

支持楼主
费心了

lhx120824

方法多样，值得推广。

jasonwqh

受用了，非常感谢！

jians0529

非常好.多种方法得出结果.向楼主学习..

yepulu

=SUMPRODUCT(COUNTIFS(INDIRECT({"数学";"化学";"物理"}&"!A:A"),A2,INDIRECT({"数学";"化学";"物理"}&"!B:B"),B2))
这里的indirect引用好。

dengjq2000

如果原始数据工作表有规律，那公式就相对更好处理了。
像主楼的附件的三个原始工作表表名分别为：Sheet1、Sheet2、Sheet3，则多工作表单条件公式为（具体见工作表 “年级单条件计数”）：

1. =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW($1:$3)&"!A:A"),A2))

复制代码

或数组公式：

1. =SUM(COUNTIF(INDIRECT("Sheet"&ROW($1:$3)&"!A:A"),A2))

复制代码

多工作表两条件或多条件公式为（具体见工作表 “年级班级双条件汇总”）：

1. =SUMPRODUCT(COUNTIFS(INDIRECT("Sheet"&ROW($1:$3)&"!A:A"),A2,INDIRECT("Sheet"&ROW($1:$3)&"!B:B"),B2))

复制代码

带着思念远行

谢谢 谢谢了

持之以恒-

非常感谢！

xieweiwei

这个不错，受教了