多条件数组在sumif、countif中的用法简介.xls

1、引题： =sum(countif(A1:A10,“sheet2、sheet3中的表二表三均引用此二表，数据一致。 SUMIF函数帮助： SUMIFSUMIF(rangerange,criteriacriteria,sum_range) Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。 Sum_range 是需要求和的实际单元格。 我们知道，sumif是一个很有用的条件求和函数，由于函数第一参数RangeRange的限制（即条件在同 列并保持与Su

2、m_rangeSum_range水平距离一致，见经典用法2），平时大家只用到了sumif函数的单条件求 和，当条件较多时，因为多条件的条件往往不在同一列，可借助辅助列用&文本合并符来并用 sumif完成，不过一般多条件求和被sum(条件1*条件2*统计区域)数组公式或者sumproduct 的用法所代替。 本文想阐述的是用sum(sumif()*)结合的一种“单列”多条件求和的方法（countif的用法类 似，就只在右边列出公式而不另外阐述了）。 复复习习一一下下SumifSumif的的经经典典用用法法吧吧 经典用法1、求表一的A字头号店铺的销售量 sumif552=SUMIF(A3:A10,

3、“A*“,C3)552 sum数组552=SUM(LEFT(A3:A10)=“A“)*C3:C10)552 sumif的第3个参数可以省略写为区域的第一个单元格，有关用法见： 1255 1255 经典用法2、求表一至表三（sheet1中）的A字头号店铺的销售量 sumif1255=SUMIF(A3:G10,“A*“,C3) sum数组1255=SUM(LEFT(A3:A10)=“A“)*C3:C10,(LEFT(D3:D10)=“A“)*F3:F10,(LEFT(G3:G10)=“A“)*I3:I10) sumif的“条件在同列并保持与Sum_range水平距离一致”这里的“同列”指的是店铺名

4、称 以A开头这个条件在A、D、G这3列即同一字段名下，而非“以A开头的店铺的狗不理销售量”这 种条件跨了不同字段。特此说明。 经典用法3、求表一至表三（注：分别在sheet1sheet3中）的A字头店铺的销售量 跨表条件求和1255=SUM(SUMIF(INDIRECT(“sheet“&1,2,3&“!A3:A10“),“A*“,INDIRECT(“sheet“&1,2,3&“!C3“) 1255 双双条条件件数数组组在在SumifSumif中中的的应应用用 1255 以上3个用法都是我们常用的，且在论坛上例子也很多。现在来个不常用的： 求表一中A字头店铺但不要A001的销售量 sumif25

5、2=SUM(SUMIF(A3:A10,“A*“,“A001“,C3)*1,-1)公式1252 sum数组252=SUM(LEFT(A3:A10)=“A“)*(A3:A10“A001“)*C3:C10)252 252=SUMIF(A3:A10,“A*“,C3)-SUMIF(A3:A10,“A001“,C3)252 其实很简单：公式1就是sumif(A3:A10,“A*“,C3)-sumif(A3:A10,“A001“,C3)的变体 求表一至表三（sheet1中）A字头店铺但不要A001的销售量572 572=SUM(SUMIF(A3:G10,“A*“,“A001“,C3)*1,-1) 572=S

6、UM(LEFT(A3:A10)=“A“)*(A3:A10“A001“)*C3:C10,(LEFT(D3:D10)=“A“)*(D3:D10“A001“)*F3:F10,(LEFT(G3:G10)=“A“)*(G3:G10“A001“)*I3:I10) 求表一至表三（sheet1中）A字头店铺的销售量，但但A001A001的的售售量量只只算算其其3 3成成。 342=SUM(SUMIF(A3:A10,“A*“,“A001“,C3)*1,-0.7)公式2776.9 342=SUMIF(A3:A10,“A*“,C3)-SUMIF(A3:A10,“A001“,C3)*0.7 公式2只算表一而已，可能有

7、误卢子 多多条条件件数数组组在在SumifSumif中中的的应应用用 4求左表中5且不等于9的数据之和 9 93 =SUM(SUMIF(A63:A82,“5“,“=9“)*1,1,-1)公式33 173 =SUM(A63:A825)*(A63:A829)*A63:A82)3 1 -9求左表中5且不等于9的数据个数 9 -1114 =SUM(COUNTIF(A63:A82,“5“,“=9“)*1,1,-1)14 -1814 =SUM(A63:A825)*(A63:A829) 6 6红灯，对于公式3请注意 -11题目是 20“求左表中5且不等于9的数据之和” -8很容易误导人产生错误的解法： 13

8、18 =SUM(SUMIF(A63:A82,“5“,“9“)*1,1,-1)错误 -11事实上，这个错误的公式求得的是： -17SUMIF(A63:A82,“5“)-SUMIF(A63:A82,“9“) 16也就是前2个条件分别求和并相加后减去第3个条件“对所有不等于9的求和”！ 4 10 多多单单元元格格条条件件数数组组在在SumifSumif中中的的应应用用 产品名称产品等级产品数量题目： 狗皮膏A20已知狗皮膏的等级及数量如左表，价格如下表，求这批产品共值多少钱。 狗皮膏D30 狗皮膏C45ABCD 狗皮膏A21100805020 狗皮膏B20 狗皮膏A15 帮助理解 56534530总

9、价 狗皮膏B3356004240225060012690 12690 =SUM(SUMIF(B87:B93,E89:H89,C87)*E90:H90)公式4 由于水平有限，希望文中描述能对您有所帮助，若有 疑义可在帖子上跟贴讨论。 gouweicao78 2006/3/14 返回引题 帮助理解 gouweicao78 2006/3/14 返回引题 求满足左边条件的店铺个数 5家 =COUNTIF(A3:A10,“A*“) 5 13家 =COUNTIF(A3:G10,“A*“) 13 13家 =SUM(COUNTIF(INDIRECT(“sheet“&1,2,3&“!A3:A10“),“A*“)

10、 13 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。 =SUM(SUMIF(INDIRECT(“sheet“&1,2,3&“!A3:A10“),“A*“,INDIRECT(“sheet“&1,2,3&“!C3“) 3家 =SUM(COUNTIF(A3:A10,“A*“,“A001“)*1,-1) 3 8家 =SUM(COUNTIF(A3:G10,“A*“,“A001“)*1,-1)8 12690 =SUM(LEFT(A3:A10)=“A“)*(A3:A10“A001“)*C3:C10

11、,(LEFT(D3:D10)=“A“)*(D3:D10“A001“)*F3:F10,(LEFT(G3:G10)=“A“)*(G3:G10“A001“)*I3:I10) 公式2只算表一而已，可能有误卢子 已知狗皮膏的等级及数量如左表，价格如下表，求这批产品共值多少钱。 表二 店铺商品销售量 A001狗皮膏114gouweicao78 B001狗尾草139 B002狗不理1612006/3/14 B001狗年旺84 B002狗皮膏64返回引题 A002狗尾草187 A001狗不理107 A002狗年旺21 表三 店铺商品销售量 A002狗皮膏182 B001狗尾草104gouweicao78 A002狗不理10 B001狗年旺1252006/3/14 B002狗皮膏187 B003狗尾草15返回引题 A003狗不理148 A001狗年旺147