减脂公式无比强大的Excel求和函数SUM(),你了解几何？

无比强大的Excel求和函数SUM(),你了解几何？






SUM()你肯定会，
你肯定也不了解，不信你试试。我们先了解一下这个函数的
说明吧。打开 SUM()函数帮助，如果你还不会可以看我以前
的讲解。

1、sum()函数如 果参数是一个数组或引用，则只计算其中的
数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。
（看下图）

2、sum()函数如果任意参数为错误值或为不能转换为数字的
文本，Excel 将会显示错误。

要计算出结果，可以：
·H4{=SUM(IFERROR(H1:H3,0))}
·H4=SUMIF(H1:H3,"<9e+307")
·H4{=SUM(IF(ISERR(H1:H3),0,H1:H3))}
·H4{=SUM(IF(ISERROR(H1:H3),0,H1:H3))}
第一、第 三和第四三个数组公式，外面的大括号不是手动加
给的，而是Shift+Ctrl+Enter获取的 ，我们叫它区域数组公式。
依据以上两点说明，SUM()函数求一行既有文本又有数字的
区域，它忽略文本只计算数字。

一、SUM()快速求和介绍：
1、对三张表汇总求和：

1第一种方法：=sum('*'!b2)一般的方法：
步骤：在汇总表B2输入sum('*'!b2)按住Ctrl+Enter
·第二种方法：按住Shift键直接鼠标点击最后一张求和工作
表;
·按住Shift键直接鼠标点击最后一张求和工作表。
2、 对一张表每个合计求和：




3、 合并单元格求和：

这个实例网 上比较多，我在W10，微软2013版操作可以实
现，sum()合并单元格其他版本无法实现。另外 ：快速求和快
捷方式用
二、SUM()函数求和介绍：
=SUM() 、=SUMPRODUCT()=、SUBTOTAL()、=SUMIF()、
=SUMIFS()
(一)、Sum()区域求和与SUMPRODUCT()区域求和，Sum()
函数必须使用 Shift+Ctrl+Enter区域数组结束，而
SUMPRODUCT()函数不使用Shift +Ctrl+Enter，因为它是内
存数组，内存数组一般要比区域数组计算快。比如：计算1
到100的和。

l Q3{=SUM(ROW(1:100))}
l Q4=SUMPRODUCT(ROW(1:100))
a) 计算车间女职工工资多少?

l E10{=SUM((RIGHT(A10:A16,2)="车间
" ;)*(B10:B16="女")*C10:C16)}
l F10=SUM PRODUCT((RIGHT(A10:A16,2)="车间
")*(B10 :B16="女")*C10:C16)
l G10=SUMIFS(C10: C16,A10:A16,"*车间
",B10:B16,"女&qu ot;)
l H10=SUMPRODUCT(--NOT(ISERR(FIND("车 间
",A10:A16)))*(B10:B16="女")*C10 :C16)
l I10=SUMPRODUCT(--NOT(ISERR(SEARCH(&quo t;*车间
",A10:A16)))*(B10:B16="女") *C10:C16)
第一个公式解释：RIGHT(A10:A16,2)="车间&qu ot;提取
右边A10:A16的两个字段等于"车间"及
(B10:B 16="女")的工资C10:C16之和，
(RIGHT(A10:A16,2 )="车间")必须要括起来。
b)计算车间男女工资之差
l E1 4=SUM(SUMIFS(C10:C16,A10:A16,"*车间
",B 10:B16,{"男","女"})*{1,-1})
公式解释：B10:B16,{"男","女
"})*{1 ,-1} ,B10:B16是性别， SUMIFS第二参数，第三
参数,{"男&quo t;,"女"}是一个常量数组，后
面 {1,-1}将前面的男和女转换成正负数值，从而计算出男女
工资之差。
l F14=SU MPRODUCT((RIGHT(A10:A16,2)="车间
")*(B1 0:B16={"
"})*(C10:C16)*{1,-1})
l G14{=SUM((RIGHT(A10:A16,2)="
男
车间
女< br>男","女
")*(B10:B16={"
"})*(C10:C16)*{1,-1})}
","
a)汇总车间女职工人数
l E18{=SUM((B 10:B16="女
")*(RIGHT(A10:A16,2)=" 车间"))}
l F18=SUMPRODUCT((B10:B16="女< br>")*(RIGHT(A10:A16,2)="车间"))
b)求男职工人数
l E21{=SUM((B10:B16="男")*1)}
l F21=SUMPRODUCT((B10:B16="男")*1)
c)求女职工工资最高前俩位工资之总和
l
E24{=SUM(SUMPRODU CT(LARGE(IF(B10:B16=B14,C10:C
16,),{1,2})))}
l
F24{=SUM(LARGE(IF(B10:B16=B15,C10:C16,), ROW(1:2)))}
d)求男职工大于100的工资和
l E27{=SUM((B1 0:B16="男
")*(C10:C16>100)*C10:C16) }
l F27=SUMPRODUCT((B10:B16="男
") *(C10:C16>100)*C10:C16)
e)求职工大于100，小于800的工资之和
l
E30{=SUM((C10: C16>100)*(C10:C16<800)*C10:C16)}
l
F 30=SUMPRODUCT((C10:C16>100)*(C10:C16<800)*C< br>10:C16)
l
G30=SUM(SUMIF(C10:C16,"& lt;"&{200,800
})*{-1,1})

f) 求一至四个车间男职工人数
l E43{=SUM((A10:A16={"一" ;,"二
","三","四"}&a mp;"车间
")*(B10:B16="男")*C10 :C16)}
l F43=SUMPRODUCT((A10:A16={"一" ;,"二
","三","四"}&a mp;"车间
")*(B10:B16="男")*C10 :C16)

(二) 隔行求和
l J3{=SUM((MOD(COLUMN($$B3:$$I3),2)=0)*$$B3:$$I3)}
l
K3=SUMPRODUCT((MOD(COLUMN($$B3:$$I3),2)=1)*$$B3:$$
I3)

(三) 求前三名销售人员销售额
l H2{=SUM(LARGE(C2:C9,{1,2,3}))}
l I2{=SUM(LARGE(C2:C9,ROW(1:3)))}
l H3=SUMPRODUCT(LARGE(C2:C9,{1,2,3}))
l H3=SUMPRODUCT(LARGE(C2:C9,ROW(1:3)))

(四) 求取区域（B9:K10）去掉一个最小值去掉一个最大值之
和
l
F9=SUM( (B6:K7))-SUM(SMALL(B6:K7,1))-SUM(LARGE(B
6:K7,1 ))
l
E9=SUMPRODUCT((B6:K7))-SUM(SMALL(B6:K 7,1))-SUM
(LARGE(B6:K7,1))

(五) 计算排名
l
C2{=SUM(($$B$$2:$$B$$7>B2)COUNTIF($$B$$2:$$B $$7,$$B$$2:$$
B$$7))+1}
l
B2=SUMPRODUCT(($$B$$ 2:$$B$$7>B2)COUNTIF($$B$$2:$$B
$$7,$$B$$2:$$B$$7))+1

l
K2=CONCATENATE(H2,TEXT(SUM((H$$2:H$$2 2=$$H2)*($$J$$
2:$$J$$22>=J2)COUNTIF(J$$2:J$$22,$$J$$ 2:$$J$$22)),"第0
名"))

(六) 求每个人的所得

l
G1{=SUM(IF(ISERR(FIND(F1,C$$ 2:C$$10)),0,1)*A$$2:A$$10*B
$$2:B$$10*(1(LEN(TRIM(C$$ 2:C$$10))-LEN(SUBSTITUTE(TRI
M(C$$2:C$$10),"
",))+1)))}
l
H1=SUMPRODUCT(ISNUMB ER(FIND(F1,C$$2:C$$10))*A$$2:
A$$10*B$$2:B$$10*(1(LEN (TRIM(C$$2:C$$10))-LEN(SUBSTIT
UTE(TRIM(C$$2:C$$10) ,"
",))+1)))
(七) 求某人三科成绩之和
l
O2{=SUM(($$A$$2:$$A$$6=K2)*NOT(ISERR(FIND(B$$1:H$$1, L2
)))*B$$2:H$$6)}
l
O3=SUMPRODUCT(($$A$$2: $$A$$6=K3)*NOT(ISERR(FIND(B
$$1:H$$1,L3)))*B$$2:H$$6)
l N2
=SUM(MMULT(--(TRANSPOSE($$B$$1:$$H$$1)=MI D(L2,{1,3,5
},2)),(ROW($$1:$$3)) )*TRANSPOSE(B2:H2))
l M2
=SUMPRODUCT((A$$2: A$$6=K2)*(1-ISERR(FIND($$B$$1:$$H$$
1,L2)))*B$$2:H$$6)
l M3
{=SUM((A$$2:A$$6=K3)*(1-ISERR(FIND($$B$$1 :$$H$$1,L3)))*B$$
2:H$$6)}

(八) 返回每个人最后一次考核分数的平均分

l
{=SUM(IF(B3:F10,& quot;",B2:F9))COUNT(B2:F2)}
l =SUMIF(B3:F10,"",B2:F9)COUNT(B2:F2)
(九) 求选定公司收入之和

l E4 {=SUM(LOOKUP(A4:C4,B1:L2))}
l F4=SUMPRODUCT(SUMIF(B1:L1,A4:C4,B2:L2))
l C6
I2
=SUM(HLOOKUP(T(IF({1},A4:C4)),$$A$$1:$$L$$2 ,2,FALSE))
l
E6{=SUM(HLOOKUP(T(IF(1,A4:C4& amp;{""})),
$$A$$1:$$L$$2,2,FALSE))}
l D7 {=SUM(COUNTIF(A4:C4,B1:L1)*B2:L2)}
l E7
=SUM(HLOOKUP(T(IF({1},A4:C4)),$$A$$1:$$L$$2,2,F ALSE))
l D8{ =SUM(OFFSET(A2,,N(IF(1,MATCH(A4:C4,B1:L1,)))))}
l
E8{ =SUM(INDEX(B2:L2,N(IF(1,MATCH(A4:C4,B1:L1,)))))}
l D9 {=SUM(N(OFFSET(A2,,MATCH(A4:C4,B1:L1,))))}
l
E9{ =MMULT(COUNTIF(A4:C4,B1:L1),TRANSPOSE(B2:L2
))}
(十) 做查询使用

l
G2{=IFERROR(SUM(($$A$$2 :$$A$$7=$$E$$2)*($$B$$2:$$B$$7=$$F$$2)
*C2:C7),"&quo t;)}
l
H2=IFERROR(SUMPRODUCT(($$A$$2:$$A$$7=$$E $$2)*($$B$$2:$$
B$$7=$$F$$2)*C2:C7),"")
(十一) 依据行A2变化求平均值

l A4=SUM(2:2)SUMIF(2:2,"<>",1:1)
l A6=SUM(2:2)SUM(OFFSET(A1,,,,COUNT(2:2)))
注意：“<>”的作用是第2行中满足不为空的对应第1
行数值求和。
(十二) 多列 (根据日期求和)

l C4 =SUM(OFFSET(C4,,MATCH(C$$3,D$$3:O$$3,)):O4)
l G13
=SUM(INDEX($$D4:$$O4,MATCH($$C$$3,$$D$$3:$$O$$3,0)):O4 )
注意：是依据C3单元格选择日期计算
(十三) 提取1至9的数字并相加求和

计算A6数字合计
l D6
{=SUM(MID(A6,ROW(IN DIRECT("1:"&LE
N(A6))),1)*1)}
l C6
=SUMPRODUCT(MID(A6,ROW(INDIRECT(" ;1:"
&LEN(A6))),1)*1)
计算文本数字
l D5
=SUM(MID(A5,ROW(INDIRECT("1:"& ;LEN
(A5))),1)*1)
l C5
=SUMPRODUCT(MID( A5,ROW(INDIRECT("1:"
&LEN(A5))),1 )*1)
计算带有字母的数字之和
l D4
{=SUM(IFERROR(MI D(A4,ROW(INDIRECT("1:"
;&LEN(A4))) ,1)1,0))}
l
C4{=SUMPRODUCT(IFERROR(MID(A4, ROW(INDIRECT(
"1:"&LEN(A4))),1)1, 0))}
(十四) 在筛选状态下求合计
通过B列名称或C列供应商筛选，求总价：
l
I1{=SUM($$D2:$$D11*$$E2:$$E11*SUBTOTAL(103,O FFSET($$E
1,ROW(1:10),)))}
lH1=SUMPRODUCT($$D 2:$$D11*$$E2:$$E11*SUBTOTAL(103,
OFFSET($$E1,ROW(1: 10),)))
l K1
{=SUM(D2:D11*SUBTOTAL(109,OFF SET(E1,ROW(1:10),)))
}
l
J1=SUMPRODUCT( D2:D11*SUBTOTAL(109,OFFSET(E1,R
OW(1:10),)))

有B1:B10，10个数据，根据筛选结果，统计大于5小于等
于30的数值个数。
l
H15{=SUM(SUBTOTAL(103,OFFSET(E1,ROW(1:99 ),))*(E2:
E100>=5)*(E2:E100<=30))}
l
I15=SUMPRODUCT(SUBTOTAL(103,OFFSET(E1,ROW(1:9< br>9),))*(E2:E100>=5)*(E2:E100<=30))

(十五) 汇总6张表数据

lB2{=SUM(SUMIF(INDIRECT( ROW($$1:$$6)&"
月!A:A"),"*" ;&$$A2&"*"
;,INDIRECT(ROW($$1:$$6 )&"月!B:B")))}
lB2=SUMPRODUCT(SUM IF(INDIRECT(ROW($$1:$$6)&
"
月!A:A&quo t;),"*"&$$A2&"*"
;,IN DIRECT(ROW($$1:$$6)&"月!B:B")))
全部讲解告一段落，总之，SUM()函数极其强大，不可小觑，
也许你还能举出更多实例，那说明 我还不与您，我还得进一
步学习。因为，360doc无法上传gif，也无法上传文件，只
能 截图加文字说明，如果需要文件可以短信联系。