SUM、SUMIF、COUNTIF函数的使用-CHENJUN.xls

1、SUM函函数数的的使使用用by chenjun 语法：E SUM(参参数数1,参参数数2,.,参参数数30) 结果：返回所有参数中的数字之和。 说明：参数最多只能有30个，并且可以省略(即,间没有参数或最后有一个,)； 参数可以为引用、返回数值和文本及逻辑值的计算表达式、数组； 参数如为引用，可以是区域联合、区域交叉、三维区域引用，只要引用不再参与数组运算就可以； 如果参数为错误值或为不能转换成数字的文本，将会导致错误。 下面作一些详细的分析： A.对对文文本本、逻逻辑辑值值及及错错误误值值的的计计算算 对引用中的文本、数字型的文本、逻辑值忽略不计算。姓名工资 3500a11000 公式=SU

2、M(H11:J14)，只计单元格中的数值，不计文本、逻辑值a22000 和I12格中的文本1000a31500 对数组中的文本、数字型的文本、逻辑值忽略不计。#N/A#DIV/0! 3500 数组公式，不带、号输入，按ctrl+shift+enter三键结束。 公式=SUM(“姓名“,“a1“,“1000“,TRUE,2000,FALSE,1500,H11:H14=“a2“) 错误值，不管是在引用、参数、还是在数组中均返回错误，此处excel的帮助中有错误。 #N/A引用中有错误值#DIV/0! 作为参数的计算表达式的结果为错误值 #VALUE! 数组中有错误值 参数或作为参数的计算表达式为不

3、是数字型的文本，返回错误。要不返回错误用F中提出的方法。 #VALUE! 直接用不是数字的文本作参数 #VALUE! 以返回不是数字的文本表达式作为参数 参数或作为参数的计算表达式为数字型的文本，转为数值后计算； 参数或作为参数的计算表达式为逻辑值时，TRUE算1，FALSE算0。 34 公式为=SUM(10,21,12,TRUE,FALSE,“2“,“2“&“0“) 其中的21为逻辑值TRUE算1，直接参数TRUE算1，其他的FALSE算0，文本2和表达式文本20转换后计算。 B.以以引引用用的的运运算算作作参参数数 区域联合86 请注意区域联合运算外的一对括号，那是不可少的，AB 此运算在

4、SUM函数中算1个参数，当SUM中的参数110 多于30个时可用此法来减少参数。211 区域交叉70 注意括号及2个引用间的空格，交叉引用312 在SUM函数中也只算1个参数，此处实际运算返回413 的是H31:K32和I29:J34相交的B31:C32区域。514 联合区域不能在数组公式中继续进行计算。615 交叉引用在数组公式中可以可以继续进行计算。 42 公式为=SUM(H29:K34 I:I)12)*(H29:K34 I:I) 实际计算的是I29:I34区域大于12的值的和 C.以以三三维维引引用用作作参参数数 63 公式为=SUM(SUM:SUMIF!H29:H34)，其中SUM:S

5、UMIF!H29:H34为对从SUM工作表开始至SUMIF工作表结束 的H29:H34的区域引用。 象这样的直接三维引用不可继续用于数组计算中。 象下面这样的数组公式为什么是可以正确运算的？ 191 公式为=SUM(H28:H34,I34,J29:K29),(H29:K34 I:I)12)*(H29:K34 I:I),SUM:SUMIF!H29:H34) 请注意上面的数组公式中SUM函数有3个参数，第1个是联合区域引用，第2个是交叉区域引用计算的数组，第3个是三维区域引用。 由于联合区域和三维引用区域均没有继续进行数组运算，所以在SUM函数的数组公式中作为单独的参数是可以的。 提示：我们在使用

6、SUM函数的数组公式时，经常只考虑有1个参数，其实是忘了SUM函数最多可有30个参数。 D.以以没没有有打打开开的的工工作作薄薄的的指指定定表表的的指指定定区区域域引引用用作作参参数数 600 公式为=SUM(C:excelhomefunctionINDIRECT函数的使用.xlsSheet2!$B:$B) 引用了C:excelhomefunctionINDIRECT函数的使用.xls 工作薄Sheet2表的整个B列。 只要路径所指定的文件存在就不需要打开文件，如不存在就返回错误。 E.以以(由由一一个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作SUM函函数数的

7、的参参数数 SUM函数不作为其他函数的参数可以使用，见G54格，1020102 公式为=SUM(INDIRECT(“H“&ROW()/2&“:J“&ROW()/2+4) 实际相当于SUM(INDIRECT(“H27:J31“)，即对H27:J31区域求和。 H54格是将这样的SUM函数放在IF函数中作为参数，就错误了， 因为ROW()返回的是一个数组并不是一个数值，这样在数组公式中INDIRECT函数返回的并不是一个二维的区域引用， 而是三维的区域引用(第3维的尺寸是1)，所以SUM的计算出错。 可以将公式改为I54格的样子(本论坛的会员提出的方法，先用SUM函数将ROW函数的数组变为数值)，

8、或用SUMIF代替(见J54格)。 E.以以(由由多多个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作SUM函函数数的的参参数数 一般是INDIRECT函数和OFFSET函数才能以多个数组元素，产生一系列对多个单元格区域的引用，返回的是三维的区域引用， SUM函数只能对第1个元素指定的区域求和，如H64格的公式。61 用内嵌SUMIF函数代替就正确了，见H65格。130 F.以以非非数数字字型型文文本本作作参参数数的的方方法法 A中指出参数或作为参数的计算表达式为不是数字型的文本，返回错误。 要解决直接参数为非数字的问题，可按图设置。#VALUE! 按图设置后，就

9、按Lotus1-2-3的方式忽略文本。见H69格。 不利因素是，所有的公式均按Loutus1-2-3的方式处理， 很多excel的表达式就会出错。 G.SUM函函数数在在数数组组公公式式中中的的一一些些应应用用 多多条条件件计计数数A部门的男性员工有几人？3 姓名部门性别工资(B92:B105=“A“)*(C92:C105=“男“)返回2个逻辑数组的乘积，基于 A1A男1000 TRUE*TRUE=1；TRUE*FALSE=0；FALSE*FALSE=0 A2B女1500 所以此处是逻辑与的关系，在excel的IF函数的条件中，0表示FALSE，非0的数值表示TRUE。 A3C女1000 去除

10、IF函数可以简化公式为3 如有2个以上并列条件，可将几个条件式相乘。 A4D女800 A、B两部门的男性员工有几人？4 A5B女2000 基于TRUE+FALSE=1；FALSE+FALSE=0；TRUE+TRUE=2 A6C男2500 而(B92:B105=“A“)和(B92:B105=“B“)不可能同时满足，所以此处是条件或的关系， A7D男1500 再乘以(C92:C105=“男“)作为并列条件。 A8A男1000 A部门所有女性员工和A部门工资1500以上的男性员工总数是多少？ A9C女10003 A10D男2000 因为(C92:C105=“女“)和(D92:D105=1500)可能

11、同时满足，所以再用NOT(NOT()转换，基于 A11A男3000 NOT(TRUE)=FALSE；NOT(FALSE)=TRUE；NOT(0)=TRUE；NOT(非0数值)=FALSE A12B男900 A13A女1800 A14A女2500 多多条条件件求求和和 A部门女性员工的工资总额是多少？4300 基于：FALSE*任何数=0；TRUE*任何数=原来的数 (B92:B105=“A“)*(C92:C105=“女“)为并列条件，*D92:D105后就是满足条件的工资。 所有女性员工的工资和男性员工工资1500以上的工资总额是多少？19600 如加IF函数就可以不用NOT(NOT()196

12、00 提示：以(C92:C105=“女“)+(D92:D105=1500)这样的形式表示条件或的关系，在条件可能同时满足时要用NOT(NOT()转换 或用IF函数判别。否则会多计数量的。 统计偶数行的工资总和是多少？1130011300 其中的(MOD(ROW(D92:D105),2)=0)就是判别是否偶数行。 特特别别提提示示：SUM函函数数在在绝绝大大多多数数的的情情况况下下用用于于数数组组公公式式中中只只能能返返回回一一个个值值，以以后后会会讲讲到到在在某某些些特特殊殊的的函函数数组组合合中中 在在多多单单元元格格数数组组公公式式中中，可可在在不不同同的的单单元元格格返返回回不不同同的的

13、值值，好好象象是是返返回回了了一一个个数数组组，但但那那只只能能在在单单元元格格 中中表表现现，而而不不能能继继续续进进行行数数组组运运算算的的。 E 中中有有很很多多的的相相关关帖帖子子，请请大大家家多多看看看看。 婚姻状况 TRUE FALSE CD 2030 2131 2232 2333 2434 2535 参数如为引用，可以是区域联合、区域交叉、三维区域引用，只要引用不再参与数组运算就可以； 参数或作为参数的计算表达式为不是数字型的文本，返回错误。要不返回错误用F中提出的方法。 其中的21为逻辑值TRUE算1，直接参数TRUE算1，其他的FALSE算0，文本2和表达式文本20转换后计算

14、。 公式为=SUM(SUM:SUMIF!H29:H34)，其中SUM:SUMIF!H29:H34为对从SUM工作表开始至SUMIF工作表结束 公式为=SUM(H28:H34,I34,J29:K29),(H29:K34 I:I)12)*(H29:K34 I:I),SUM:SUMIF!H29:H34) 请注意上面的数组公式中SUM函数有3个参数，第1个是联合区域引用，第2个是交叉区域引用计算的数组，第3个是三维区域引用。 由于联合区域和三维引用区域均没有继续进行数组运算，所以在SUM函数的数组公式中作为单独的参数是可以的。 提示：我们在使用SUM函数的数组公式时，经常只考虑有1个参数，其实是忘了S

15、UM函数最多可有30个参数。 102 引用了C:excelhomefunctionINDIRECT函数的使用.xls 工作薄Sheet2表的整个B列。 因为ROW()返回的是一个数组并不是一个数值，这样在数组公式中INDIRECT函数返回的并不是一个二维的区域引用， 可以将公式改为I54格的样子(本论坛的会员提出的方法，先用SUM函数将ROW函数的数组变为数值)，或用SUMIF代替(见J54格)。 一般是INDIRECT函数和OFFSET函数才能以多个数组元素，产生一系列对多个单元格区域的引用，返回的是三维的区域引用， 按图设置后，就按Lotus1-2-3的方式忽略文本。见H69格。 不利因素

16、是，所有的公式均按Loutus1-2-3的方式处理， 很多excel的表达式就会出错。 (B92:B105=“A“)*(C92:C105=“男“)返回2个逻辑数组的乘积，基于 TRUE*TRUE=1；TRUE*FALSE=0；FALSE*FALSE=0 所以此处是逻辑与的关系，在excel的IF函数的条件中，0表示FALSE，非0的数值表示TRUE。 如有2个以上并列条件，可将几个条件式相乘。 基于TRUE+FALSE=1；FALSE+FALSE=0；TRUE+TRUE=2 而(B92:B105=“A“)和(B92:B105=“B“)不可能同时满足，所以此处是条件或的关系， A部门所有女性员工

17、和A部门工资1500以上的男性员工总数是多少？ 因为(C92:C105=“女“)和(D92:D105=1500)可能同时满足，所以再用NOT(NOT()转换，基于 NOT(TRUE)=FALSE；NOT(FALSE)=TRUE；NOT(0)=TRUE；NOT(非0数值)=FALSE 提示：以(C92:C105=“女“)+(D92:D105=1500)这样的形式表示条件或的关系，在条件可能同时满足时要用NOT(NOT()转换 特特别别提提示示：SUM函函数数在在绝绝大大多多数数的的情情况况下下用用于于数数组组公公式式中中只只能能返返回回一一个个值值，以以后后会会讲讲到到在在某某些些特特殊殊的的函

18、函数数组组合合中中 在在多多单单元元格格数数组组公公式式中中，可可在在不不同同的的单单元元格格返返回回不不同同的的值值，好好象象是是返返回回了了一一个个数数组组，但但那那只只能能在在单单元元格格 COUNTIF函函数数的的使使用用 语法： COUNTIF(引引用用,条条件件) 结果：计算引用所指定的区域内满足条件的单元格的数目。 说明：一般情况下引用只能是对一个工作表的一个区域的引用，但实际应用中可以用以数组指定的多个区域(也就是数组返回的三维引用)； 条件为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件， 当条件为文本时可以用统配符*(表示

19、任意多的字符)和?(表示单个字符)，如果要查找不是统配符的*和?字符，可用*和?表示。 提示：引用必须是指区域不能是数组，COUNTIF函数一般是返回一个数值，但如果引用是数组指定的多个区域，可以返回一个数组； 当条件为数组时也返回一个同尺寸的数组。 A.对对一一个个区区域域引引用用的的计计算算 11 公式为=COUNTIF(H28:K34,“20“)，求H28:K34区域中20的数值单元格数量。 上例中的条件也可以是引用别的单元格的，如：条件20 11 公式改为=COUNTIF(H28:K34,“&G13) 不能直接用三维引用，如：#VALUE! 这样的公式=COUNTIF(SUM:SUMI

20、F!H28:K34,“20“)返回错误！怎么计算下面会讲到。 可以用交叉区域引用，如：11 公式=COUNTIF(28:34 H:K),“20“)的引用区域同H28:K34 不能用联合区域引用，如：#VALUE! 公式=COUNTIF(J29:K30,J33:K34),“20“)是错误的 当用2个条件时，必须其中的1个条件为TRUE时包括了另一个条件为FALSE的范围，或2个条件为TRUE的范围不重复。 如求区域H28:K34中满足20并且20包含了=30的区域，可按下面的公式 5 公式=COUNTIF(H28:K34,“20“)-COUNTIF(H28:K34,“=30“)5 求区域H28:

21、K34中满足30的单元格数，30不会同时满足，可用下面的公式 17 公式=COUNTIF(H28:K34,“30“)17 以数组作条件，也可写成这样17 公式=SUM(COUNTIF(H28:K34,“30“)中为常量数组，可以不按数组公式输入。 此时COUNTIF函数按数组条件返回了2个元素的数组，再用SUM求和。 B.在在条条件件中中使使用用统统配配符符 A5B5C5D5AB A6ABA6C6AD6*110 A*7BA17C7AD7*211 BA8A8C8AD8*312 A9B9C9D9413 求区域A28:D32中以A开头的单元格数514 6 公式=COUNTIF(A28:D32,“A*

22、“)，*表示任意长度的字符。615 求区域A28:D32中以A开头以6结束的单元格数 2 公式=COUNTIF(A28:D32,“A*6“) 求区域A28:D32中包含6的单元格数 4 公式=COUNTIF(A28:D32,“*6*“) 求区域A28:D32中第3位为A的单元格数 4 公式=COUNTIF(A28:D32,“?A*“)，?表示一个任意字符。 求区域A28:D32中包含*的单元格数 4 公式=COUNTIF(A28:D32,“*“)，第1个*为统配符，*表示*字符，最后1个*为统配符，要表示字符*用*转换。 求区域A28:D32中第3位为*的单元格数 2 公式=COUNTIF(A

23、28:D32,“?*“)，*表示字符*，其他的是统配符。 求区域A28:D32中包含的单元格数 3 公式=COUNTIF(A28:D32,“*“)，其中的表示以免把*当作*字符。 求区域A28:D32中包含的单元格数 1 公式=COUNTIF(A28:D32,“*“)，其中的表示2个字符。 求区域A28:D32中包含*的单元格数 2 公式=COUNTIF(A28:D32,“*“)，其中的*表示2个*字符。 提提示示：统统配配符符只只能能对对文文本本有有效效，对对数数值值无无效效，如如求求H29:K34区区域域中中含含1的的单单元元格格数数 0 因引用区域中是数值，公式=COUNTIF(H29:

24、K34,“*1*“)无效，可用SUM的数组公式解决。 C.比比较较条条件件对对数数字字型型文文本本和和数数值值的的区区别别 求右面区域中等于12的单元格数 2 公式=COUNTIF(K55:K64,“12“)，用等于条件计数时，对文本型数字和数值一样对待。 如求大于12的单元格数会怎样？55 3 公式=COUNTIF(K55:K64,“12“)的结果是错的，只在数值单元格中计数。 怎样改呐？一个供参考的方案 5公式=COUNTIF(K55:K64,“12“)+COUNTIF(K55:K64,“12A“)为2段相加， 其中“12A“实际是强制对文本格与文本12A比较。 如用作条件会怎样了？如求不

25、等于12的格数8 9 公式=COUNTIF(K55:K64,“12“)实际统计了不等于数值12的格数，文本12被当作不等的。 如非得这样算，建议的方案 8公式=COUNTA(K55:K64)-COUNTIF(K55:K64,“12“)返回了正确的结果。 提提示示：实实在在没没有有必必要要将将文文本本型型数数字字和和数数值值混混在在一一起起，那那简简直直是是自自找找麻麻烦烦，设设计计表表格格时时就就应应该该做做到到类类型型一一致致。 D.将将数数字字型型数数据据类类型型统统一一成成文文本本格格式式就就不不会会出出错错吗吗？ 看右面的18位身份证号码区域，单元格中均是文本。(只是为了说明问题，并不

26、是真正的号码) 求区域中320101197001012011号码出现了几次？1 6 公式=COUNTIF(K69:K74,“=320101197001012011“)，为什么结果是错的？ 原因是：比较条件为等于时，“=320101197001012011“实际是比较数值，文本型数字是先转为数值 再同条件中的数比较的，而关键是excel对数值的有效位最多为15位，超出的3位在条件 和文本型数字转换时均被忽略了，想想看，忽略了后3位在本例中那不就全部相同了！ 在全部为18位的数字型文本，怎么才能正确比较呐？关键就是要强制让excel按文本来比较，我建议的方案如下： 1 公式=COUNTIF(K69

27、:K74,“=3201011*97001012011“) 在在作作为为条条件件的的号号码码数数字字中中间间任任一一位位置置插插入入一一个个统统配配符符，那那样样excel就就只只能能以以文文本本来来比比较较了了！ E.以以对对别别的的工工作作薄薄的的区区域域引引用用作作参参数数 统计C:excelhomefunctionINDIRECT函数的使用.xlsSheet2!$B$2:$B$4中大于150的格数 #VALUE! 提提示示：必必须须打打开开被被引引用用的的工工作作薄薄，否否则则COUNTIF函函数数返返回回错错误误！ F.统统计计空空格格 3 公式=COUNTIF(J83:J89,“)，

28、注意条件用“，如果用“=“，那么结果是错的， 1 公式=COUNTIF(J83:J89,“=“)只统计没有内容的单元格数。 以以下下内内容容涉涉及及用用数数组组产产生生对对区区域域的的三三维维引引用用 G.以以由由数数组组指指定定的的区区域域引引用用作作参参数数 A中讲到直接用三维区域引用=COUNTIF(SUM:SUMIF!H28:K34,“20“)是错的，可用INDIRECT函数 的数组参数产生一个三维引用 33 数组公式=SUM(COUNTIF(INDIRECT(“SUM“,“COUNTIF“,“SUMIF“&“!H28:K34“),“20“) 其中INDIRECT函数用常量数组产生对3

29、个工作表H28:K34区域的引用，COUNTIF函数也返回3个元素的数组， SUM函数再对数组求和。 参考INDIRECT函数的使用一文中“关于工作表名的几个宏表函数名称定义”，就可以在INDIRECT函数中 用名称代替数组常量统计各表中指定区域满足条件的单元格数。 详见G97格的链接关于工作表名的几个宏表函数名称定义 再举一个例子：求下面区域中间隔列(蓝色列)中满足20的单元格数 129102920291 525102520255 921102120219 13171017201713 17131013201317 21910920921 25525520525 29129120129 8

30、公式=SUM(COUNTIF(INDIRECT(“R99C“&ROW(1:4)*2-1&“:R106C“&ROW(1:4)*2-1,0),“20“) INDIRECT返回4个区域引用，并作为COUNTIF函数的引用参数，COUNTIF函数返回4个值的数组， 选中公式中COUNTIF(.)部分按F9就可看到这一点。SUM函数求总和。 特特别别提提示示：因因为为COUNTIF函函数数的的第第1个个参参数数必必须须用用区区域域引引用用，所所以以就就可可以以正正确确计计算算由由数数组组参参数数指指定定的的多多个个区区域域引引用用。 由由数数组组参参数数指指定定的的区区域域引引用用我我们们暂暂且且称称之

31、之为为“三三维维引引用用”，大大部部分分的的函函数数是是不不支支持持这这样样的的引引用用的的。 上例也可以用SUM函数的多条件数组方法求得，但如果数据列所包含的行数很多，上面的公式计算要快得多。 H.以以由由数数组组指指定定的的区区域域引引用用作作参参数数 求A99:G106区域中出现数值的个数(重复出现的只算1次)？ 10 公式=SUM(1/COUNTIF(A99:G106,A99:G106) 因为COUNTIF函数的条件参数是引用了一个单元格区域，实际计算时先将引用转换为数组，然后按每个数组作为条件， 返回8行7列的结果数组，每个元素为针对每个条件数组元素的结果，1/COUNTIF的结果如

32、下： 0.20.166670.166670.1666670.1250.166670.2 0.20.166670.166670.1666670.1250.166670.2 0.20.20.166670.20.1250.20.2 0.20.20.166670.20.1250.20.2 0.20.20.166670.20.1250.20.2 0.20.20.166670.20.1250.20.2 0.1666670.20.166670.20.1250.20.16667 0.1666670.20.166670.20.1250.20.16667 可以看出返回的每个元素是相应单元格的值在区域中出现次数的倒

33、数，再用SUM函数求和就是区域中不同元素的个数。 提提示示：我我还还是是要要提提醒醒excel对对实实数数的的计计算算是是有有误误差差的的，用用SUM函函数数求求和和后后可可能能出出现现不不是是整整数数，建建议议再再用用ROUND函函数数圆圆整整。 10 公式=ROUND(SUM(1/COUNTIF(A99:G106,A99:G106),0) 这这样样就就可可以以避避免免实实数数的的计计算算误误差差了了，一一定定是是返返回回唯唯一一出出现现的的个个数数。 COUNTIF函数的第2个条件参数可以为表达式计算出的数组，更多的用法请看E 论坛中的相关帖子， 如果COUNTIF函数的第1个参数是数组产

34、生的引用、第2个参数是数组或多单元格区域引用，那么COUNTIF函数 返回的也是一个数组，其运算规则见A134格中链接帖子的1楼“数组的特殊用途”中关于“数组运算规则”的论述。 数组的一些特殊用途 CD 2030 2131 2232 2333 2434 2535 说明：一般情况下引用只能是对一个工作表的一个区域的引用，但实际应用中可以用以数组指定的多个区域(也就是数组返回的三维引用)； 条件为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件， 当条件为文本时可以用统配符*(表示任意多的字符)和?(表示单个字符)，如果要查找不是统配符的*和?字符

35、，可用*和?表示。 提示：引用必须是指区域不能是数组，COUNTIF函数一般是返回一个数值，但如果引用是数组指定的多个区域，可以返回一个数组； 这样的公式=COUNTIF(SUM:SUMIF!H28:K34,“20“)返回错误！怎么计算下面会讲到。 公式=COUNTIF(28:34 H:K),“20“)的引用区域同H28:K34 公式=COUNTIF(J29:K30,J33:K34),“20“)是错误的 当用2个条件时，必须其中的1个条件为TRUE时包括了另一个条件为FALSE的范围，或2个条件为TRUE的范围不重复。 公式=COUNTIF(A28:D32,“*“)，第1个*为统配符，*表示*

36、字符，最后1个*为统配符，要表示字符*用*转换。 9 10文本型 11文本型 12文本型 13文本型 14文本型 11 数值型 12 数值型 13 数值型 14 数值型 15 数值型 320101197001012011 320101197001012012 320101197001012013 320101197001012014 320101197001012015 320101197001012016 由=“产生的空白格 由=“产生的空白格 0 数值0 没有内容的格 a1文本 0文本0 23 数值 因引用区域中是数值，公式=COUNTIF(H29:K34,“*1*“)无效，可用SUM的数

37、组公式解决。 公式=COUNTIF(K55:K64,“12“)，用等于条件计数时，对文本型数字和数值一样对待。 公式=COUNTIF(K55:K64,“12“)实际统计了不等于数值12的格数，文本12被当作不等的。 提提示示：实实在在没没有有必必要要将将文文本本型型数数字字和和数数值值混混在在一一起起，那那简简直直是是自自找找麻麻烦烦，设设计计表表格格时时就就应应该该做做到到类类型型一一致致。 原因是：比较条件为等于时，“=320101197001012011“实际是比较数值，文本型数字是先转为数值 再同条件中的数比较的，而关键是excel对数值的有效位最多为15位，超出的3位在条件 和文本型

38、数字转换时均被忽略了，想想看，忽略了后3位在本例中那不就全部相同了！ 在全部为18位的数字型文本，怎么才能正确比较呐？关键就是要强制让excel按文本来比较，我建议的方案如下： 在在作作为为条条件件的的号号码码数数字字中中间间任任一一位位置置插插入入一一个个统统配配符符，那那样样excel就就只只能能以以文文本本来来比比较较了了！ A中讲到直接用三维区域引用=COUNTIF(SUM:SUMIF!H28:K34,“20“)是错的，可用INDIRECT函数 数组公式=SUM(COUNTIF(INDIRECT(“SUM“,“COUNTIF“,“SUMIF“&“!H28:K34“),“20“) 其中I

39、NDIRECT函数用常量数组产生对3个工作表H28:K34区域的引用，COUNTIF函数也返回3个元素的数组， 参考INDIRECT函数的使用一文中“关于工作表名的几个宏表函数名称定义”，就可以在INDIRECT函数中 关于工作表名的几个宏表函数名称定义 公式=SUM(COUNTIF(INDIRECT(“R99C“&ROW(1:4)*2-1&“:R106C“&ROW(1:4)*2-1,0),“20“) INDIRECT返回4个区域引用，并作为COUNTIF函数的引用参数，COUNTIF函数返回4个值的数组， 特特别别提提示示：因因为为COUNTIF函函数数的的第第1个个参参数数必必须须用用区区

40、域域引引用用，所所以以就就可可以以正正确确计计算算由由数数组组参参数数指指定定的的多多个个区区域域引引用用。 由由数数组组参参数数指指定定的的区区域域引引用用我我们们暂暂且且称称之之为为“三三维维引引用用”，大大部部分分的的函函数数是是不不支支持持这这样样的的引引用用的的。 上例也可以用SUM函数的多条件数组方法求得，但如果数据列所包含的行数很多，上面的公式计算要快得多。 因为COUNTIF函数的条件参数是引用了一个单元格区域，实际计算时先将引用转换为数组，然后按每个数组作为条件， 返回8行7列的结果数组，每个元素为针对每个条件数组元素的结果，1/COUNTIF的结果如下： 可以看出返回的每个

41、元素是相应单元格的值在区域中出现次数的倒数，再用SUM函数求和就是区域中不同元素的个数。 提提示示：我我还还是是要要提提醒醒excel对对实实数数的的计计算算是是有有误误差差的的，用用SUM函函数数求求和和后后可可能能出出现现不不是是整整数数，建建议议再再用用ROUND函函数数圆圆整整。 COUNTIF函数的第2个条件参数可以为表达式计算出的数组，更多的用法请看E 论坛中的相关帖子， 如果COUNTIF函数的第1个参数是数组产生的引用、第2个参数是数组或多单元格区域引用，那么COUNTIF函数 返回的也是一个数组，其运算规则见A134格中链接帖子的1楼“数组的特殊用途”中关于“数组运算规则”的

42、论述。 SUMIF函函数数的的使使用用 语法： SUMIF(引引用用1,条条件件,引引用用2) 结果：按引引用用1中满足条条件件的单元格位置求引引用用2中对应位置的单元格中数值的总和。 说明：引引用用1和引引用用2两个参数必须是对区域的引用或由数组所定义的多个区域的引用，不能是直接的数组或由表达式返回的数组； 引引用用2参数可以省略，省略时相当于同引引用用1参数； 条条件件参数的用法同COUNTIF函数，其形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件，可以用统配符。 对引引用用2的求和方式同SUM函数，对引引用用2中的文本、数字型的文本、逻辑值忽略不计算，如果引引用用2中包含

43、错误值返回错误。 提提示示：SUMIF函函数数一一般般只只返返回回一一个个数数值值；当当条条件件为为数数组组时时可可以以返返回回一一个个数数组组； 由由于于第第1第第3参参数数必必须须是是对对区区域域的的引引用用，所所以以可可以以用用以以数数组组定定义义的的多多个个区区域域引引用用(我我们们暂暂称称之之为为“由由数数组组产产生生的的三三维维引引用用”)， 当当参参数数为为“由由数数组组产产生生的的三三维维引引用用”时时，SUMIF函函数数返返回回一一个个同同尺尺寸寸的的数数组组，数数组组运运算算的的规规则则请请看看我我的的 “数数组组的的一一些些特特殊殊用用途途”一一贴贴。数组的一些特殊用途

44、因因为为很很多多的的用用法法与与SUM和和COUNTIF中中的的相相同同，下下面面就就说说得得简简单单一一点点 A.省省略略引引用用2参参数数 310 公式=SUMIF(H28:K34,“20“)，求出区域H28:K34中20的数值总和，省略第3个参数就在第1个参数指定的区域中求和。 B.不不省省略略参参数数 165 公式=SUMIF(H28:I34,“10“,J28:K34)，注意2个引用区域的行列数要相同， 在H28:I34中按10条件返回J28:K34中对应格中值的和。 C.简简化化B中中第第3个个参参数数写写法法的的方方法法 这是E 中一位网友提出的用法。 165 公式=SUMIF(H

45、28:I34,“10“,J28)，其第3个参数只写左上单元格引用 SUMIF函数会自动按第1个参数的尺寸大小扩展第3个参数的区域引用。 这样写法的麻烦是如果J28:K34区域中变化的不是J28格，公式不会自动重算。 大家可以改变一个格中的值试试！ AB D.2个个引引用用参参数数均均可可以以引引用用整整列列110 165 公式=SUMIF(H:I,“10“,J:K)211 312 413 E.以以“由由数数组组产产生生的的三三维维引引用用”作作参参数数514 求出12个月的分表汇总的各地区的销售总额615 地区销售总额 东24000 公式=SUM(SUMIF(INDIRECT(ROW($1:$

46、12)&“月!A:A“),$A36&“*“,INDIRECT(ROW($1:$12)&“月!B:B“)， 南24000 然后向下拖动。 西24000 INDIRECT(ROW($1:$12)&“月!A:A“)就是由数组产生的对12个月表的A列的引用， 北36000 $A36&“*“是使用带统配符的条件。 求出H29:K34区域中各行之和最大2个值的平均值79 在这里见证了mmult函数的强大，呵呵卢子 79 公式=AVERAGE(LARGE(SUMIF(INDIRECT(“H“&ROW($H$29:$K$34)&“:K“&ROW($H$29:$K$34),“0“),ROW($1:$2) IND

47、IRECT(“H“&ROW($H$29:$K$34)&“:K“&ROW($H$29:$K$34)就是数组产生的对每一行的引用。 求出H29:K34区域中行的和70的有几行？33 3 公式=SUM(1*(SUMIF(OFFSET($H$28:$K$28,ROW($H$29:$K$34)-ROW($H$28:$K$28),),“0“)70) OFFSET($H$28:$K$28,ROW($H$29:$K$34)-ROW($H$28:$K$28),)也是另一种由数组产生的对每一行的引用， SUMIF函数再对这个三维引用求和并返回一个6行元素组成的数组，70比较运算再产生一个逻辑数组， SUM函数再求

48、逻辑数组中为TRUE的个数。 曾经在Excelhome中出现过的问题，求下面区域中有50出现的行的最大间隔行数？ 54535150475347 49485347484750 47505152485153 48455046515253 47475152495454 49505052494947 45474949404946 42465446514749 49545149514651 49505452505349 3 公式太长了，而且比我在原贴处的复杂，这儿的用法只是为了说明SUMIF函数对三维引用的计算法。 F.如如果果引引用用是是对对其其他他工工作作薄薄区区域域的的引引用用，那那个个工工作作薄

49、薄必必须须打打开开，否否则则就就返返回回错错误误 G.按按非非空空白白格格的的条条件件计计算算 计算右面区域中K列为非空白格对应的L列数值的和 111101 公式=SUMIF(K63:K68,“,L63:L68)是错的， 条件“将输入=“的空白格计算在内了 我建议的公式如下 110101 公式=SUM(L63:L68)-SUMIF(K63:K68,“,L63:L68) 分2部分计算再相减，在COUNIF函数中讲过条件“包含了 没有输入的空白格及输入=“产生的空白格。 为为了了写写这这三三个个函函数数用用了了我我一一个个月月的的时时间间，有有很很多多会会员员多多次次问问我我好好了了吗吗？ 现现在在终终于于可可以以交交卷卷了了！ 希希望望多多提提意意见见，可可在在原原贴贴处处跟跟贴贴。 E chenjun 20