《2007函数sumifs和countifs的深入理解》由会员分享，可在线阅读，更多相关《2007函数sumifs和countifs的深入理解（8页珍藏版）》请在金锄头文库上搜索。
1、2007函数SUMIFS 和COUNTIFS的深入理解今天突然对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,
2、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(SU
3、MIFS(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(SUMIF
4、S(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、客户ABC的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(C
5、2:C22*(A2:A22=C)*(B2:B22=1,3,4)如果再次增多就可以看到SUMIFS的优势了。大家可以看到，SUMIFS在7和8的情况下，字符明显减少。（当然上面的情况好些还能用MMULT完成，但感觉SUMIFS更加简洁易懂）大家一起来探讨一下，这个新函数的还有什么新特性。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:A
6、22,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的13月份数量=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)
7、替代公式：=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列到更多列。 本帖最后由 mxqchina 于 2009-5-24 12:19 编辑 附件: 您所在的用
8、户组无法下载或查看附件把最复杂的变成最简单的,才是最高明的。-达芬奇若是数组公式，按CTRL+SHIFT+ENTER三键结束公式。 原文是COUNTIFS($M$6:$M$2001,= & $AV14,$M$6:$M$2001,0,$M$6:$M$2000,=& $AV6, $M$6:$M$2000,= & $AW6)。sumifs的函数参数分别为：sum_range，criteria_range1，criteria1，criteria_range2，criteria2，sum_range自然是求和的目标列，criteria_range1是条件数据列，criteria1是条件本身。大于数值，就
9、直接用双引号；大于某单元格数值，就在双引号后用&连接单元格名称。最佳答案假如这列数据在A列并从A1开始。 在B1输入取汉字公式：=LEFT(A1,LENB(A1)-LEN(A1) 在C1输入取数字公式：=RIGHT(A1,2*LEN(A1)-LENB(A1) 向下复制公式即可。 时间:2008-3-10 7:51:14作 者:UMVSOFT整理摘要:将阿拉伯数字转换为汉字数字,支持到百万亿正文:例子: Debug.Print UpNumber(-10556765765555.45,0,True ) 显示为: 负壹拾万伍仟伍佰陆拾柒亿陆仟伍佰柒拾陆万伍仟伍佰伍拾伍圆肆角零分 Public Fun
10、ction UpNumber(ByVal Number As Double, Optional ByVal Typ As Long, Optional ByVal IsMoney As Boolean) As String * - 将阿拉伯数字转换为大写字符串 Version 1.0 2002-02-06 Version 1.1 2002-04-05 修改到支持到千亿 Version 1.2 2004-08-14 修改为支持 Typ,IsMoney 参数,转换结果可以不是金额,支持到百万亿 Roadbeg - - 参数说明: Number 待转换的数字,可以是小数. Typ 转换类型,可选值 0,1 0 转换为 零,壹,贰 等 1 转换为 一,二,三 等 IsMoney 是否是金额,如果是,则转换为多少元,小数后转换为多少角,分,反之则转换为类似于二点三这种形式 - - 返回值说明: 如果成功,返回转换后的字符串 如果失败,返回空字符串 - - 注意,此函数最大只支持到百万亿 没有对 Typ 的值进行检查,如果 Typ 不为 0,1 之一,将会引发错误. 另,由于 Double 类型数值范围的原因,超过百万亿,将不能显示小数,同样的超过十万亿只能显示一个小数,以此类推. - * On Error GoTo Doerr Dim Result As String 返回值
