Excel教程:一个万能的函数SUMPRODUCT

函数SUMPRODUCT是一个集计数与求和于一身的函数,数组公式中函数SUMPRODUCT能取代函数SUM且不用按<Ctrl+Shift+Enter>三键结束。

函数定义:

在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

语法格式:

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

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

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

1、忽略文本求和

输入公式:

=SUMPRODUCT(A2:A10)

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

公式就是SUMPRODUCT({1;0;2;0;3;0;0;0;5}),即返回11

2、文本型数值求和

输入公式:

=SUMPRODUCT(B2:B9*1)

B2:B9单元格数值是文本型,*1是将文本型数值转换成数值型

可以用“--”、“+0”、“-0”、 “/1”、“^1”等等来转换

3、单条件计数

求业绩大于等于5000的个数:

输入公式:

=SUMPRODUCT(N(B2:B9>=5000))

B2:B9>=5000部分返回{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}

条件成立返回逻辑值TRUE,条件不成立返回逻辑值FALSE

函数N:将不是数值形式的值转换为数值形式。日期转换为序列值,TRUE转换成1,其他值转换成0。

同样的,也可以用“--”、“+0”、“-0”、 “/1”、“^1”、“*1”等等来转换

N(B2:B9>=5000)部分返回{0;1;1;0;0;1;1;0}

用函数SUMPRODUCT求和返回4

4、单条件求和

求业绩大于等于5000的和:

输入公式:

=SUMPRODUCT((B2:B9>=5000)*B2:B9)

(B2:B9>=5000)部分返回{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}

发生四则运算时,TRUE相当于1,FALSE相当于0

(B2:B9>=5000)*B2:B9部分返回{0;7800;6521;0;0;16660;8054;0}

用函数SUMPRODUCT求和返回39035

5、多条件计数

求销售一部业绩大于等于5000的个数:

输入公式:

=SUMPRODUCT((B2:B9='销售一部')*(C2:C9>=5000))

多条件计数语法:

SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)* (条件区域N=条件N))

6、多条件求和

求销售一部业绩大于等于5000的和:

输入公式:

=SUMPRODUCT((B2:B9='销售一部')*(C2:C9>=5000)*C2:C9)

多条件求和语法:

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

或者:

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

该题也可以用公式:

=SUMPRODUCT((B2:B9='销售一部')*(C2:C9>=5000),C2:C9)

多条件求和语法中求和条件前使用“*”与“,”的区别:

当求和区域中没有文本时,“*”与“,”可以互相通用;

当求和区域中有文本时使用“,”,若使用“*”会返回错误值#VALUE!

7、单条件计算不重复个数

求业务员不重复的个数:

输入公式:

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

COUNTIF(A2:A9,A2:A9)部分统计区域A2:A9中每个单元格内容在该区域中出现的次数,返回{2;1;2;3;1;1;3;3}

1/{2;1;2;3;1;1;3;3}是将重复数据算作1,比如出现2次的是1/2,出现3次的是1/3,

1/COUNTIF(A2:A9,A2:A9)部分返回

{0.5;1;0.5;0.333333333333333;1;1;0.333333333333333;0.333333333333333}

用函数SUMPRODUCT求和返回5

8、区间求和

求业绩大于等于5000、小于15000的和:

输入公式:

=SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000)*B2:B9)

或者:

=SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000),B2:B9)

9、隔列求和

求入库、出库合计:

输入公式:

=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3),右拉下拉填充。

10、数组相乘

求总提成:

输入公式:

=SUMPRODUCT(B2:B9*C2:C9)

B2:B9*C2:C9返回每个业绩*对应提成比例,即返回{45.06;390;326.05;136.89;2001.5;1466;402.7;138.75}

然后用函数SUMPRODUCT求和

11、有条件的数组相乘

求“销售一部”的总提成:

输入公式:

=SUMPRODUCT((A2:A9='销售一部')*C2:C9*D2:D9)

(A2:A9='销售一部')条件成立返回TRUE,条件不成立返回FALSE,即{TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

然后*C2:C9返回的是销售一部的业绩,即{1502;7800;0;0;20015;14660;0;4625}

再*D2:D9返回的就是销售一部对应的提成,即{45.06;390;0;0;2001.5;1466;0;138.75}

最后用函数SUMPRODUCT求和

12、带'~'求和

求业务员中有带“~”的业绩和:

输入公式:

=SUMPRODUCT(ISNUMBER(FIND('~',B2:B9))*C2:C9)

函数SUMPRODUCT不能使用通配符,但可以与函数FIND相结合完成对应功能

FIND('~',B2:B9)部分找到“~”在各业务员字符串中的位置,没有“~”返回错误值#VALUE!,该部分返回{2;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!}

函数ISNUMBER:判断一个值是否为数值,返回TRUE或FALSE。

ISNUMBER(FIND('~',B2:B9))部分返回{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

再*C2:C9返回带“~”的各业务员的业绩{1502;0;6521;0;0;14660;0;0}

最后用函数SUMPRODUCT求和。

(0)

相关推荐

  • 手把手教你,学会排名

    中国式排名 使用RANK类函数排名时,如果出现并列的情况,并列者将占用名次,因此会导致部分名次出现空缺.而按 照中国人习惯的排名计算方法,无论有几个并列第1名,之后的排名仍然是第2名,即并列排名不占用 ...

  • 这个Excel查找匹配问题着实难住了我,猜你也不会

    前几天小必老师无意中做到了一个题目,是关于一个查询的问题,其中这个查询呢,是带有合并单元格的.下面的问题仅提供于训练函数的解决思维,实际工作中可提供自身的情况选择适合自己的一些就方法. 题目:按右边的 ...

  • 为什么说0和1是Excel函数逻辑运算的核心要义?

    每天一篇Excel技术图文 微信公众号:Excel星球 NO.723-0和1的故事 作者:看见星光  微博:EXCELers / 知识星球:Excel HI,大家好,我是星光. 之前我们花费很大的篇幅 ...

  • 不重复数值的计算(sumproduct函数的运用)

    一.计算不重复数值的个数 L3单元格中的公式为: =SUMPRODUCT(1/COUNTIF($D$3:$D$22,$D$3:$D$22)) 公式的含义是先用countif计算D3:D22区域中每个数 ...

  • 一个万能的函数,sumproduct

    函数SUMPRODUCT是一个集计数与求和于一身的函数,数组公式中函数SUMPRODUCT能取代函数SUM且不用按<Ctrl+Shift+Enter>三键结束. 函数定义: 在给定的几组数 ...

  • Excel教程:FIND+MID函数,乱序产品规格中提取型号

    Excel中,在一串字符串里面提取部分内容,根据工作表格实际情况,我们可以使用分列.快速填充.函数公式或者PQ等方法来实现. 下面表格里面,A列是产品规格,需要将"DP-"这一段字 ...

  • Excel教程:和IF函数说再见!用这个方法搞定逻辑判断,不要太简单!

    每天一点小技能 职场打怪不得怂 编按:逻辑值在Excel运算中,是一个奇妙的存在.在很多需要写函数或一长串公式去做判断的地方,只要你学会巧妙运用逻辑值的方法,就能更快更准确的解决问题.你不信?看完今天 ...

  • 比VLOOKUP厉害10倍,它才是Excel中的“万能”引用函数

    全套Excel视频教程,扫码观看 编按: 哈喽,大家好!在上篇文章<揭秘!这个高手私藏的求和公式,3分钟完成你半天的工作量!>中,我们分享了如何在杂乱无序的数据中,统计前几名数据的合计.公 ...

  • Excel教程:不会函数也能汇总数据

    合并计算这个功能在excel各个版本都有,能够帮助用户将单元格区域中的数据,按照项目的匹配,对同类数据进行汇总,汇总的方式包括求和.计数.平均值.最大值.最小值等等.不会函数的同学也能够轻松实现数据汇 ...

  • Excel教程:IS系列函数汇总!建议收藏!

    今天跟大家讲解含ISNUMBER在内的4个重要的IS相关函数. ISBLANK函数 ISBLANK函数主要作用是判断引用的单元格是否为数值. 如上表中D5单元格为空白,我们可以通过ISBLANK函数来 ...

  • Excel教程:魔性的TEXT函数,看一眼就心动~

    在Excel的函数里有一个神奇的函数,这个函数就是TEXT.函数的结构非常简单,只需要两个参数:TEXT(数据,格式代码),今天为大家分享这个函数的六个妙用. 1.将日期变为星期 有这样的一个销售明细 ...

  • Excel教程:必会函数之IF函数实战案例

    IF函数在我们的实际工作中应用非常多,因为我们要不停的进行各种条件判断,为了完成这些判断,最直接.最方便就是使用IF函数来完成.如何正确灵活的使用IF函数,这是接下来介绍的重点. [一] 语法格式 I ...

  • Excel教程:SUMPRODUCT函数统计学分

    现在需要在G列,统计每个学生的总学分. G5单元格公式为: =SUMPRODUCT((B5:F5>=60)*($B$2:$F$2)) 然后下拉,就可以一次性统计出所有学生的学分. 用SUMPRO ...