万能函数

万能函数：SUMPRODUCT函数

一、基本用法

{=SUM(B2:B8*C2:C8)} 按CTRL+SHIFT+回车

方法1：=SUMPRODUCT($B$2:$B$8,$C$2:$C$8)

方法2：=SUMPRODUCT($B$2:$B$8*$C$2:$C$8)

$B$2:$B$8：第一组数

*：用*等于两组数相乘后作为sumproduct的一个参数，其计算结果等同于两个参数

$C$2:$C$8)：第二组数

两种方法的区别在于：

SUMPRODUCT函数的两个参数之间的连接符号不同，方法1用逗号 , 连接，方法2用乘号 * 连接。

注意：

1、数组参数必须具有相同的维数。否则，函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。

2、如果当数据源中包含文本数据时，使用方法1依然可以返回正确结果

但使用方法2会导致文本和数值相乘，返回错误值#VALUE!

3、函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

SUMPRODUCT(array1,[array2], [array3], ...)

Array1：必需。其相应元素需要进行相乘并求和的第一个数组参数。

Array2, array3,...：可选。 2 到 255 个数组参数，其相应元素需要进行相乘并求和。

计数：=SUMPRODUCT((条件1)*(条件2)*…(条件n))

求和：=SUMPRODUCT((条件1)*(条件2)*…(条件n)*数据区域)

案例：

=SUMPRODUCT((B26:B32="A")*C26:C32)

公式分解

在四则运算里,true等同于1。FASLE等同于0

二、单条件计数

=SUMPRODUCT(N(B2:B13="男"))

=COUNTIF(B2:B13,"男")

三、多条件计数

=COUNTIFS(B2:B13,"男",C2:C13,">80")

=SUMPRODUCT((B2:B13="男")*(C2:C13>80))

四、多条件求和

=SUMIFS(C2:C13,B2:B13,"男",C2:C13,">80")

=SUMPRODUCT((B2:B13="男")*(C2:C13>80)*C2:C13)

五、模糊条件求和

=SUMPRODUCT(ISNUMBER(FIND("销售",C2:C13))*(B2:B13="男")*D2:D13)

=SUMIFS($D$2:$D$13,$C$2:$C$13,"销售*",$B$2:$B$13,"男")

六、多列汇总求和

=SUMIF($A$2:$A$9,A12,$B$2:$G$9)û

=SUMPRODUCT(($A$2:$A$9=A16)*$B$2:$G$9)

SUMPRODUCT函数条件求和语法：

=SUMPRODUCT((条件1)*(条件2)*(条件n)*求和区域)

七、隔列分类汇总

=SUMPRODUCT(($B$2:$M$2=N$2)*$B3:$M3)

=SUMPRODUCT(($B$2:$M$2=O$2)*$B3:$M3)

=SUMIF($B$2:$M$2,$N$2,B3:M3)

=SUMIF($B$2:$M$2,$O$2,B3:M3)

八、多权重综合评价

=SUMPRODUCT(B$2:E$2,B3:E3)

=RANK(F3,$F$3:$F$14)

九、二维区域条件求和

根据左侧的数据源，按产品和姓名统计销售额

=SUMPRODUCT(($A$2:$A$13=F$1)*($B$2:$B$13=$E2)*$C$2:$C$13)

十、多表多条件求和

=SUMPRODUCT(SUMIFS(INDIRECT(ROW(1:5)&"!C:C"),INDIRECT(ROW(1:5)&"!A:A"),A2,INDIRECT(ROW(1:5)&"!B:B"),B2))

温馨提示：以上难度系数过高，请绕道而行

十一、实战案例