查找符合２个条件的值+SUMPRODUCT函数

查找符合２个条件的值
查找与右表中品名和型号相符的代码，返回相应的值？

＝可以在表1增加辅助列，表2用vlookup引用，如附件所示 ：

或=SUMPRODUCT((A2=Sheet1!$A$2:$A$15)*(B2=Sheet1!$B$2:$B$15)*Sheet1!$C$2:$C$15)

一个条件，用vlookup；多个条件，用数组：sumproduct就是数组，在a2:a15里面找a2,b2:b15里面找b2,然后在c2:c15里面找到满足条件的值

=INDEX(Sheet1!$C$2:$C$15,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$15&Sheet1!$B$2:$B$15,))数组公式。

=LOOKUP(1,0/((A2=Sheet1!$A$2:$A$15)*(Sheet2!B2=Sheet1!$B$2:$B$15)),Sheet1!$C$2:$C$15)

SUMPRODUCT函数
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT（array1,array2,array3, ...）SUMPRODUCT(数组1,数组2,数组3,……) 返回数组（区域）中对应项相乘后的和。

sum就是简单求和; SUMIF就是有条件的求和; SUMPRODUCT就是数组求和法.

　　Array1,array2,array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。

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

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

例如下图所示的工作表数据：

要求将列A和列B中同一行的数据相乘后的结果相加，即：

A1×B1+ A2×B2+ A3×B3+…+ A10×B10

可使用如单元格C3中的公式：

=SUMPRODUCT(A1:A10,B1:B10)

或=SUMPRODUCT(DataA,DataB)，如果将单元格区域A1：A10命名为“DataA”，将单元格区域B1：B10命名为“DataB”

 

（应用）基于两个条件汇总数据

例如，下表所示的数据：

华东区域产品B的销售量=SUMPRODUCT((C2:C10)*(A2:A10=”华东”)*(B2:B10=”B”))＝结果为100。

其中： C2：C10为包含销售量的单元格区域；通过A2：A10=”华东”产生一个数值为1，0的数组，即如果列A中的数据为华东则值为1，否则为0；同理，通过B2:B10=”B”产生一个数值为1，0的数组；SUMPRODUCT函数将这些数组分别相乘后相加即得到结果

 

如果不合适地应用SUMPRODUCT函数，将会得不到正确的结果，如，

现在要求华中区域与目标日时间差为负数的项目的销售额。

其中，将单元格区域“A2:A10”命名为“区域”，将单元格区域“B2:B10”命名为“销售额”，将单元格区域“C2:C10”命名为“时差”。

如果我们输入下面的公式： =SUMPRODUCT((时差<0),(区域="华中"),销售额)，将得不到正确的结果。由于某种原因，SUMPRODUCT函数不能正确处理布尔值，因此公式无效。

下面的公式在SUMPRODUCT函数中将布尔值乘以1，将得到正确的结果：

=SUMPRODUCT(1*(时差<0),1*(区域="华中"),销售额)

或=SUMPRODUCT(销售额*(区域=”华中”)*(时差<0))

其中，布尔值包括true和false两个值。　　在逻辑中，真值或逻辑值是指示一个陈述在什么程度上是真的。在计算机编程上多称作布尔值。　　在经典逻辑中，唯一可能的真值是真和假。但在其他逻辑中其他真值也是可能的: 模糊逻辑和其他形式的多值逻辑使用比简单的真和假更多的真值。　　在代数上说，集合 {真,假} 形成了简单的布尔代数。可以把其他布尔代数用作多值逻辑中的真值集合，但直觉逻辑把布尔代数推广为 Heyting代数。

 

B      C      D         E

1  Array1  Array2  Array3    Array4

2   3      4        2          7

3   8      6        6          7

4   1      9        5          3

　　公式 说明（结果）

=SUMPRODUCT(B2:C4,D2:E4) 两个数组的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3。(156)

 

 

=SUMPRODUCT((G1:G3="男")*(E1:E3<=60))

这个公式的意思是统计，G1－G3是男的，同时E1－E3数值小于等于60的人数。

首先这是一个数组公式，要按Ctrl+Shift+Enter结束。

然后看计算过程：假如G1＝男，G2，G3都为女，然后E1＝65，E2＝60，E3＝80。

这时候公式变为=SUMPRODUCT((TRUE，FALSE，FALSE)*(TRUE，FALSE，FALSE))

理解：因为G1=男，所以第一个值为TRUE。第二个不为男，值就为FALSE。接下来，TRUE和FALSE分别代表1和0。所以公式变为： =SUMPRODUCT((1，0，0)*(1，0，0)) ；然后接下来就是SUMPRODUCT的计算过程了=1*1+0*0+0*0=1，所以最后的结果等于1。

 

SUMPRODUCT函数是返回乘积之和，SUMPRODUCT（A1:A4,B1:B4)=A1*B1+A2*B2+A3*B3+A4*B4,这是此函数的基本用法.

另：

1、怎样用SUMPRODUCT函数统计出“岗位”为“干部”，而且“性别”为“女”的人数？

   =SUMPRODUCT((D2:D9="干部")*(A2:A9="女"))

 2、怎样用SUMPRODUCT函数统计出“岗位”为“工人”，而且“性别”为“女”，而且25<“年龄”<40的人数？   

   =SUMPRODUCT((D2:D9="工人")*(A2:A9="女")*(B2:B9<40)*(B2:B9>25))

举例：SUMPRODUCT（（周一！M28：M34=“龙腾”）*（周一！P28：P34））

公式的意思是：P28:P34中对应于M28:M34中是“龙腾”的单元格的数据之和，假如：M29,M31,M32中为“龙腾”，则公式值等于P29+P31+P32.

诀窍：用*号分隔组合条件

特别说明：区域的选择必须是具体的区域，不能是整个一列，否则会提示错误。

 

条件1：日期<=2008-2-29

条件2,（销售管理!U8:U115="合同")

条件3（销售管理!Y8:Y115="颜敬")或者（销售管理!Y8:Y115="敬颜"）

三个条件同时满足，第三个条件有一个并列条件：

=SUMPRODUCT((销售管理!T8:T115<=DATE(2008,2,29))*(销售管理!U8:U115="合同")*((销售管理!Y8:Y115="颜敬")+(销售管理!Y8:Y115="敬颜")),销售管理!M8:M115)

“且”用 * 号， “或” 不能用OR 而是用 + 号：至于OR这个函数，不能用在这里。在这个数组公式中，每一个条件得到的应该是一个数组，而不是单一值（当然有特殊的情况）。用 + 能得出一个数组来，用OR却必然返回单一值。这是重大的区别。