多条件加总和多条件计数

biaogewang

http://www.biaogewang.com/tech.php?id=20100809

SUMPRODUCT是一个很特别的函数。它原本的用途是把两个或以上数组对应的数值相乘后把乘积相加。 可是更多时候，我们会把它用作多条件加总/多条件计数。就是在2007版中，Excel增加了SUMIFS（多条件加总）和 COUNTIFS（多条件计数）两个函数，用SUMPRODUCT 还是有SUMPRODUCT的优势。
首先，SUMPRODUCT在定义条件的时候，由于是直接用等式或其他比较公式作为条件， 比SUMIFS和COUNTIFS更灵活。
第二，学会用SUMPRODUCT去做多条件加总/多条件计数后， 会更容易理解SUMPRODUCT的其他用途，如"RANKIF"（条件排序）， 或多条件VLOOKUP等。
另外，（也可能是习惯的问题）SUMPRODUCT直接用公式作为条件，看上去更直观。
我们先看看用SUMPRODUCT简单替代SUMIF/COUNTIF。

	A	B
1	a	1
2	c	2
3	A	3
4	b	4
5	a	5

比如说，针对左边两列数值，我们需要加总所有第一列是 "a" 或 "A" 对应第二列的值（1+3+5）。 直接用SUMIF，公式就是：
=SUMIF(A1:A5,"a",B1:B5)
A1:A5是条件范围，"a"是条件的要求数值，B1:B5是加总范围。
如果用SUMPRODUCT，公式就是：=SUMPRODUCT((A1:A5="a")*1,B1:B5)

AB各元素公式结果1 a1(A1="a")*112 c2(A2="a")*103 A3(A3="a")*114 b4(A4="a")*105 a5(A5="a")*11

公式里的第一个数组(A1:A5="a")*1反映了条件，我们把这数组中五个元素分别在左边表格的第三列中列出。
第一个元素是(A1="a")*1， A1="a"会返回 “TRUE” ， SUMPRODUCT会把所有非数值的元素忽略，以我们要把 “TRUE” / “FALSE” 变成数值 1 或 0。
一般使用的方法是把条件乘以1，也可以在前面加两个负号。 比如(A1="a")*1，(A2="a")*1 ... 或
--(A1="a")，--(A2="a") ...
这样，第一个数组返回的，就是右边表格中第四列的值。SUMPRODUCT((A1:A5="a")*1,B1:B5) 实际上就是 “1*1 + 0*2 + 1*3 + 0*4 + 1*5” ，等于符合条件的的就加总，不符合条件的的就不加。

实际上SUMPRODUCT并不一定要求两个或以上的数组，一个数组也是可以的。所以有时候写公式的时候，会直接把要乘的都放第一个数组里：
=SUMPRODUCT((A1:A5="a")*B1:B5)
我自己的习惯是把条件都放在第一个数组，把加总的放在第二个数组，方便自己纠错的时候分析。
要注意的是，条件前后必须用（），不然，由于乘号的优先顺序比较高， A1:A5="a"*B1:B5就会被视同为A1:A5=("a"*B1:B5)，公式自然就报错了。
要做COUNTIF，跟上面SUMIF的方式很类似， 只需要把*B1:B5改成*1就可以了。如下：
=SUMPRODUCT((A1:A5="a")*1)
说SUMPRODUCT比SUMIFS和COUNTIFS更灵活， 是因为SUMPRODUCT的条件是用公式定义的。比如说在上面的例子中，如果只需要加小写的"a"，不加大写的"A"， 用SUMIF就只能加辅助列，计算那个单元格是小写的"a"；用SUMPRODUCT就可以一步到位了：
=SUMPRODUCT(EXACT(A1:A5,"a")*B1:B5)
理解了上面单个条件加总/计数，要设计多条件加总/多条件计数，实际上就是用:
=SUMPRODUCT((条件一)*(条件二)*(条件三),加总范围)

	A	B	C
1	A	a	1
2	B	c	2
3	B	a	3
4	C	b	4
5	A	a	5

比如要把A列=A，B列=a 对应的C列数字加总：
=SUMPRODUCT((A1:A5="A")*(B1:B5="a"),C1:C5)
要计算A列=A，B列=a 的个数：
=SUMPRODUCT((A1:A5="A")*(B1:B5="a"))
只要多于一个条件，就不用 *1 或者 -- 了。

galz01

sumproduct除了帮助中用途之外，还可以代替rank 排序，代替sumif做多条件加总，代替 countif做多条件计数。

tygg

学习了，谢谢

lxbwf521

总结的好，天天在用，但是没好好总结一下。

junsbi

只有一个条件，用sumif快点

byeamb

原帖由 galz01 于 2010-8-12 16:42 发表
sumproduct除了帮助中用途之外，还可以代替rank 排序，代替sumif做多条件加总，代替 countif做多条件计数。

请问sumproduct如何做多条件计数,举个最简单例子:
          A列奶站  B列蛋白  C列乳糖
1            1号         3             4
2            2号         4             5
3            1号         3             3
4            3号         4             2
5            1号         4             5
6            2号         2             3
7            2号         4             2   
8            3号         3             2
9            1号         2             3
10          3号         5             2         

比如求1号奶站合格奶源的数量(假定条件:蛋白和乳糖都大于2)
如何用sumproduct或countif来求这个多条件计数?多谢!

byeamb

另外楼主的sump为何要乘以1呢,,,,,

kszcs

学习的好贴字。收了

byeamb

没人理我  高手请进.....

zhuying0511

谢谢楼主辛苦整理.