2007版函数-SUMIFS和COUNTIFS的深入理解

楼主 mxqchina	今天突然对sumifs的应用有了点兴趣。个人觉得可以取代部分sumproduct的多条件求和功能。 1、客户A的销售额 =SUMIFS(C2:C22,A2:A22,"A") 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")) =SUMIF(A2:A22,"A",C2:C22) 2、客户A的1月份销售额 =SUMIFS(C2:C22,A2:A22,"A",B2:B22,1) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22=1)) 3、客户A的1月份和3月份销售额 =SUM(SUMIFS(C2:C22,A2:A22,"A",B2:B22,{1,3})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3})) 4、客户A和C的销售额 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22={"A","C"})) =SUM(SUMIF(A2:A22,{"A","C"},C2:C22)) 5、客户A和C的1月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,1)) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22={"A","C"})(B2:B22=1)) 6、客户A的1月份和客户C的3月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1,3})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22={"A","C"})(B2:B22={1,3})) 7、客户A和客户C的1月份和3月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3})) 注意此公式7和公式6的差异仅为{1,3}和{1;3}中间的符号。可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3}))+SUMPRODUCT(C2:C22(A2:A22="C")(B2:B22={1,3})) 8、客户A和客户C的1月份\3月份\4月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3;4})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22(A2:A22="C")(B2:B22={1,3,4})) 9、客户A\B\C的1月份\3月份\4月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","B","C"},B2:B22,{1;3;4})) 替代公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22(A2:A22="B")(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22(A2:A22="C")(B2:B22={1,3,4})) 如果再次增多就可以看到SUMIFS的优势了。大家可以看到，SUMIFS在7和8的情况下，字符明显减少。（当然上面的情况好些还能用MMULT完成，但感觉SUMIFS更加简洁易懂）大家一起来探讨一下，这个新函数的还有什么新特性。。。。。先在excelhome上发了，理睬的不多。发到这里，大家一起看看。。。 10、客户A的数量 =COUNTIFS(A2:A22,"A") 替代公式： =SUMPRODUCT(--(A2:A22="A")) =COUNTIF(A2:A22,"A") 11、客户A和B的数量 =SUM(COUNTIFS(A2:A22,{"A","B"})) 替代公式： =SUMPRODUCT(--(A2:A22={"A","B"})) =SUM(COUNTIF(A2:A22,{"A","B"})) 12、客户A和B的1月份数量 =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,1)) 替代公式： =SUMPRODUCT((A2:A22={"A","B"})(B2:B22=1)) 13、客户A和B的1\3月份数量 =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1;3})) 替代公式： =SUMPRODUCT((A2:A22={"A","B"})(B2:B22=1))+SUMPRODUCT((A2:A22={"A","B"})(B2:B22=3)) 如果条件更多，COUNTIFS的优势就显现出来了。 14、客户A的1月份和客户B的3月份数量 =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1,3})) 替代公式： =SUMPRODUCT((A2:A22={"A","B"})(B2:B22={1,3})) 15、客户和月份的不重复个数 =SUMPRODUCT(1/COUNTIFS(A2:A22,A2:A22,B2:B22,B2:B22)) 替代公式： =SUMPRODUCT(--(MATCH(A2:A22&B2:B22,A2:A22&B2:B22,)=ROW(A2:A22)-1)) =SUMPRODUCT(1/COUNTIF(D2:D22,D2:D22)) （D列为辅助列）感觉这个是今天最有收获的公式。这个公式可以适用3列、4列到更多列。。。。。 SUMIFS的深入应用_mxqchina.zip SUMIFS和COUNTIFS的深入应用_mxqchina.zip
2楼 jackeroo	呵呵，这里理睬的也不多。用2003的比较多，不过发现SUMIFS还是很好用的。
3楼 toby08	呵，对新函数要多多挖掘，更高版本的Excel会慢慢为人们所接受只是一个趋势的问题。Office2010也增加了不少新函数，一些旧函数如RANK等将转化为“兼容性质”的函数了（还是拿RANK来说，它好像将被RANK.EQ所替代），尽早适应新函数的用法还是一个明智的选择。
4楼 apolloh	很好！谢谢分享。ET最欢迎这样的文章，三天内如果无人提出异议，此文即加精华。
5楼 mxqchina	更新了一下，加入COUNTIFS的应用。大家一起看看第15个公式，返回多列不重复的个数，个人觉得比较有用。
6楼 helingping16	可替换的那两个我倒是很常用，SUMIFS这个都没用过。呵呵，有空的时候也要去试下
7楼 gql	是的，旧的东西是会被替代的，不断地学习新的东西才能适应社会的发展。。谢谢LZ
8楼 zjylsjwz	初体验：比原来多了个S
9楼牙齿晒太阳	很好，又学到了不少知识
10楼雷鸣	虽然还不太懂，但还是先拿下来存起慢慢看，谢了前辈！
11楼 opelwang	感谢分享。好像在哪见过，excelhome社区吧。
12楼 tryourbest	谢谢分享，先收场了回去研究
13楼 wlc2008	了解了解，学习
14楼 xcd	非常不错!
15楼 ahaz	假设C1:C460区域内存放着职工的职称，D1:D460区域存放着职工的性别。如果要统计具有高级职称的男性职工总数，可以使用公式“=SUM（IF（C1:C460=″高级″，IF（D1:D460=″男″，1，0）））”。这是一个数组公式，输入结束后按住Ctrl+Shift回车即可计算出结果。想问一下这个 IF（D1:D460=″男″，1，0)是什么意思？？
16楼 73hjj	看了，应用一下.....................
17楼 hbcnc001	仔细研究下
18楼 wangqilong1980	oooO ↘┏━┓ ↙ Oooo ( 踩)→┃踩┃ ←(踩 ) \ ( → ┃√┃ ← ) / \_ )↗┗━┛↖(_ /
19楼 herenqing	仔细学习，收获不小！
20楼 wangqilong1980	非常好用，谢谢版主。
21楼 thomascwh	谢谢楼主分享!
22楼 gln5775	找了一天才找到,一个公式弄得人好辛苦
23楼健康快乐123	是很好的文章，应该支持一下
24楼 homier	学习中。
25楼 rhr2008	谢谢老师！学习了！
26楼 laijin858	sumproduct是2007版和2010版对2003版多条件计数、求和函数的改进，理应多学习学习。当然，旧的函数有时候有必要学习、运用。
27楼传递	恩好好学习一下
28楼悠悠雨	学习
29楼 feishifan	比较有系统的总结，谢谢楼主。TKS！
30楼二姐	如果想用SUMIFS求某中心下某部门的某个人的某一项费用怎么写？？还是说有其它函数？？我用数据透视弄了一下，但想用SUMIFS在试一下，不知怎么写，烦写帮忙回复，谢谢
31楼 mxqchina	今天突然对sumifs的应用有了点兴趣。个人觉得可以取代部分sumproduct的多条件求和功能。 1、客户A的销售额 =SUMIFS(C2:C22,A2:A22,"A") 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")) =SUMIF(A2:A22,"A",C2:C22) 2、客户A的1月份销售额 =SUMIFS(C2:C22,A2:A22,"A",B2:B22,1) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22=1)) 3、客户A的1月份和3月份销售额 =SUM(SUMIFS(C2:C22,A2:A22,"A",B2:B22,{1,3})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3})) 4、客户A和C的销售额 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22={"A","C"})) =SUM(SUMIF(A2:A22,{"A","C"},C2:C22)) 5、客户A和C的1月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,1)) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22={"A","C"})(B2:B22=1)) 6、客户A的1月份和客户C的3月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1,3})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22={"A","C"})(B2:B22={1,3})) 7、客户A和客户C的1月份和3月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3})) 注意此公式7和公式6的差异仅为{1,3}和{1;3}中间的符号。可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3}))+SUMPRODUCT(C2:C22(A2:A22="C")(B2:B22={1,3})) 8、客户A和客户C的1月份\3月份\4月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","C"},B2:B22,{1;3;4})) 可替换公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22(A2:A22="C")(B2:B22={1,3,4})) 9、客户A\B\C的1月份\3月份\4月份销售额合计 =SUM(SUMIFS(C2:C22,A2:A22,{"A","B","C"},B2:B22,{1;3;4})) 替代公式： =SUMPRODUCT(C2:C22(A2:A22="A")(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22(A2:A22="B")(B2:B22={1,3,4}))+SUMPRODUCT(C2:C22(A2:A22="C")(B2:B22={1,3,4})) 如果再次增多就可以看到SUMIFS的优势了。大家可以看到，SUMIFS在7和8的情况下，字符明显减少。（当然上面的情况好些还能用MMULT完成，但感觉SUMIFS更加简洁易懂）大家一起来探讨一下，这个新函数的还有什么新特性。。。。。先在excelhome上发了，理睬的不多。发到这里，大家一起看看。。。 10、客户A的数量 =COUNTIFS(A2:A22,"A") 替代公式： =SUMPRODUCT(--(A2:A22="A")) =COUNTIF(A2:A22,"A") 11、客户A和B的数量 =SUM(COUNTIFS(A2:A22,{"A","B"})) 替代公式： =SUMPRODUCT(--(A2:A22={"A","B"})) =SUM(COUNTIF(A2:A22,{"A","B"})) 12、客户A和B的1月份数量 =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,1)) 替代公式： =SUMPRODUCT((A2:A22={"A","B"})(B2:B22=1)) 13、客户A和B的1\3月份数量 =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1;3})) 替代公式： =SUMPRODUCT((A2:A22={"A","B"})(B2:B22=1))+SUMPRODUCT((A2:A22={"A","B"})(B2:B22=3)) 如果条件更多，COUNTIFS的优势就显现出来了。 14、客户A的1月份和客户B的3月份数量 =SUM(COUNTIFS(A2:A22,{"A","B"},B2:B22,{1,3})) 替代公式： =SUMPRODUCT((A2:A22={"A","B"})(B2:B22={1,3})) 15、客户和月份的不重复个数 =SUMPRODUCT(1/COUNTIFS(A2:A22,A2:A22,B2:B22,B2:B22)) 替代公式： =SUMPRODUCT(--(MATCH(A2:A22&B2:B22,A2:A22&B2:B22,)=ROW(A2:A22)-1)) =SUMPRODUCT(1/COUNTIF(D2:D22,D2:D22)) （D列为辅助列）感觉这个是今天最有收获的公式。这个公式可以适用3列、4列到更多列。。。。。 SUMIFS的深入应用_mxqchina.zip SUMIFS和COUNTIFS的深入应用_mxqchina.zip
32楼 jackeroo	呵呵，这里理睬的也不多。用2003的比较多，不过发现SUMIFS还是很好用的。
33楼 toby08	呵，对新函数要多多挖掘，更高版本的Excel会慢慢为人们所接受只是一个趋势的问题。Office2010也增加了不少新函数，一些旧函数如RANK等将转化为“兼容性质”的函数了（还是拿RANK来说，它好像将被RANK.EQ所替代），尽早适应新函数的用法还是一个明智的选择。
34楼 apolloh	很好！谢谢分享。ET最欢迎这样的文章，三天内如果无人提出异议，此文即加精华。
35楼 mxqchina	更新了一下，加入COUNTIFS的应用。大家一起看看第15个公式，返回多列不重复的个数，个人觉得比较有用。
36楼 helingping16	可替换的那两个我倒是很常用，SUMIFS这个都没用过。呵呵，有空的时候也要去试下
37楼 gql	是的，旧的东西是会被替代的，不断地学习新的东西才能适应社会的发展。。谢谢LZ
38楼 zjylsjwz	初体验：比原来多了个S
39楼牙齿晒太阳	很好，又学到了不少知识
40楼雷鸣	虽然还不太懂，但还是先拿下来存起慢慢看，谢了前辈！
41楼 houtian23
42楼 houtian23	{"A","C"} {1;3}这两个随便一个是分号一个是逗号就行了，返回都是正确值但不能是同是分号或者逗号，如果同是分号那就是一维纵向数组，A对应1，C对应3，这就是 6、客户A的1月份和客户C的3月份销售额合计，逗号亦然。如果符号不同，那分号那个是一维纵向数组，逗号是一维横向数组，按照数组自适应理论，这两个数组会自动扩充（如下图），这样就是7、客户A和客户C的1月份和3月份销售额合计
43楼笑依然	SUMIF 函数我只会简单的有条件有引用楼上太复杂了呵
44楼 dirkhe	万分感谢你的解答。昨天我还是试了，再引入一个维度（比如说季度）用一个逗号两个分号的情况 {"A","C"}{1;3}{1;2},得出的结果是A11，C11，A32，C32的和不是想象中的A11，A12，C11，C12，A31，A32，C31，C32的和总的来说用分号最好只使用一次，不要出现两次这样逻辑比较好理解。逗号出现多次无所谓
45楼 feishifan	还是Sumifs 的比较好用，速递比较快/
46楼陈皮皮小皮	收了。谢谢。
47楼 jianggang	可以保存啊。条件如下： "=11“ 或者是： "="&11
48楼 houtian23	还不明白啊假设A1：A5都值是1,2,3,4,5 你写公式=SUMIFS(A1:A5,A1:A5,">3")没问题但是写=SUMIFS({1;2;3;4;5},{1;2;3;4;5},">3")就提示错误前面那个叫单元格引用，后边这个叫数值回去好好看看帮助什么引用吧
49楼心痛神伤	存了先
50楼心痛神伤	又学了一个函数
51楼 lpsafcz	虽然还不太懂，但还是先拿下来存起慢慢看，谢了
52楼 ludenic	很好，不断更新新知识。
53楼 lpsafcz	下载分享了，感谢无私奉献！
54楼冷冷的春天	为什么文件下载失败呢
55楼 poiuyman5	强大
56楼 achanzh	谢谢楼主分享
57楼 yeminqiang	下来看看先，函数都有用
58楼相国寺	谢谢
59楼相国寺	能告诉我你的qq吗？
60楼陈韵123	ss
61楼〆樱木嘉儿╰	新手伤不起~~呜呜
62楼 ann_lee89	觉得函数真心有点难，不过我会耐着性子好好学习的，感谢网友的分享。
63楼 yirenxiangtao
64楼 james-z	非常感谢
65楼 johnmark	看不懂，下载后好好研究下。
66楼林柔小小	学习啦~
67楼 wumin88838	经典的东西
68楼老百姓	支持支持
69楼老百姓	感觉这个是今天最有收获的公式。这个公式可以适用3列、4列到更多列
70楼老百姓	125G焙烤扁桃仁
71楼〆樱木嘉儿╰	新手伤不起~~呜呜
72楼 ann_lee89	觉得函数真心有点难，不过我会耐着性子好好学习的，感谢网友的分享。
73楼 yirenxiangtao
74楼 james-z	非常感谢
75楼 johnmark	看不懂，下载后好好研究下。
76楼林柔小小	学习啦~
77楼 wumin88838	经典的东西
78楼老百姓	支持支持
79楼老百姓	感觉这个是今天最有收获的公式。这个公式可以适用3列、4列到更多列
80楼老百姓	125G焙烤扁桃仁