Excel函数sumproduct应用案例-多条件求和
作者：iamlaosong
越来越认为sumproduct这个函数实用，过去用sum组函数。改起来复制起来都麻烦，sumif在条件多的时候也认为不方便。
如今改用sumproduct函数，就简单多了。查过sumproduct函数的用法，其解释为“求二个或二个以上数组的乘积之和”，假设因此就片面地理解为这与多条件求和无关。那就错了。事实上呢。利用条件真假的值（“真”相应值为1，“假”相应值为0），sumproduct函数用起来要比sumif函数好用的多。
比如。“=SUMPRODUCT(（A1:A100=“project师”）*1)”就统计了A列中含有“project师”的行数。须要注意的是，后面“*1”是不可缺少的。否则结果为零。以下举几个应用案例。
1、库存统计
Excel文件里有三张表，一张入库记录，一张出库记录，一张库存记录。在加入出入库记录后。库存表用sumproduct函数自己主动反应当前库存。
（1）入库记录表A、B、C、D、E、F列
| 入库时间 | 单据号 | 零件号 | 货物数量 | 包装种类 | 质量状态 |
| 2013-06-04 | A1307703 | F114001721 | 72 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001722 | 36 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001723 | 72 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001724 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001725 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001726 | 120 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001727 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307703 | F114001728 | 24 | 料箱 | 合格 |
| 2013-06-04 | A1307708 | F114001729 | 144 | 料箱 | 合格 |
| 2013-06-04 | A1307708 | F114001730 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307708 | F114001731 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307708 | F114001732 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307708 | F114001733 | 72 | 料箱 | 合格 |
| 2013-06-04 | A1307708 | F114001734 | 72 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001728 | 144 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001729 | 144 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001730 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001731 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001732 | 48 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001733 | 144 | 料箱 | 合格 |
| 2013-06-04 | A1307714 | F114001734 | 144 | 料箱 | 合格 |
| 2013-06-04 | A1307719 | F114001735 | 72 | 料箱 | 合格 |
| 2013-06-04 | A1307719 | F114001736 | 36 | 料箱 | 合格 |
| 2013-06-04 | A1307719 | F114001737 | 144 | 料箱 | 合格 |
（2）出库记录表A、B、C、D、E、F列
| 出库时间 | 单据号 | 零件号 | 货物数量 | 包装种类 | 质量状态 |
| 2013-06-06 | 0000003 | F114001721 | 576 | 料箱 | 合格 |
| 2013-06-06 | 0000005 | F114001722 | 192 | 料箱 | 合格 |
| 2013-06-06 | 0000006 | F114001723 | 240 | 料箱 | 合格 |
| 2013-06-06 | 0000007 | F114001724 | 252 | 料箱 | 合格 |
| 2013-06-07 | 0000008 | F114001725 | 288 | 料箱 | 合格 |
| 2013-06-07 | 0000008 | F114001726 | 288 | 料箱 | 合格 |
| 2013-06-07 | 0000008 | F114001727 | 144 | 料箱 | 合格 |
| 2013-06-07 | 0000009 | F114001728 | 432 | 料箱 | 合格 |
| 2013-06-07 | 0000010 | F114001729 | 216 | 料箱 | 合格 |
| 2013-06-07 | 0000010 | F114001730 | 360 | 料箱 | 合格 |
| 2013-06-07 | 0000010 | F114001731 | 144 | 料箱 | 合格 |
| 2013-06-07 | 0000011 | F114001732 | 144 | 料箱 | 合格 |
| 2013-06-07 | 0000012 | F114001733 | 72 | 料箱 | 合格 |
| 2013-06-07 | 0000013 | F114001734 | 360 | 料箱 | 合格 |
| 2013-06-07 | 0000014 | F114001728 | 120 | 料箱 | 合格 |
| 2013-06-07 | 0000016 | F114001729 | 72 | 料箱 | 合格 |
| 2013-06-07 | 0000016 | F114001730 | 118 | 料箱 | 合格 |
| 2013-06-07 | 0000016 | F114001731 | 144 | 料箱 | 合格 |
| 2013-06-07 | 0000016 | F114001732 | 144 | 料箱 | 合格 |
| 2013-06-08 | 0000018 | F114001733 | 72 | 料箱 | 合格 |
| 2013-06-08 | 0000018 | F114001734 | 72 | 料箱 | 合格 |
| 2013-06-08 | 0000019 | F114001735 | 216 | 料箱 | 合格 |
| 2013-06-08 | 0000019 | F114001736 | 216 | 料箱 | 合格 |
| 2013-06-08 | 0000020 | F114001737 | 192 | 料箱 | 合格 |
（3）库存记录表A、B、C、D、E、F、G列
| 零件号 | 货物名称 | 包装种类 | 质量状态 | 合计入库 | 合计出库 | 库存数量 |
| F114001721 | YN3 HousingLH | 料箱 | 合格 | 1116 | 1564 | 72 |
| F114001722 | YN3 HousingRH | 料箱 | 合格 | 492 | 1163 | 36 |
| F114001723 | YN3 LensLH | 料箱 | 合格 | 668 | 2295 | 72 |
| F114001724 | YN3 LensRH | 料箱 | 合格 | 600 | 2183 | 48 |
| F114001725 | YP7 HousingLH | 料箱 | 合格 | 312 | 1152 | 48 |
| F114001726 | YP7 HousingRH | 料箱 | 合格 | 624 | 926 | 120 |
| F114001727 | YP7 LensLH | 料箱 | 合格 | 1008 | 636 | 48 |
| F114001728 | YP7 LensRH | 料箱 | 合格 | 600 | 2308 | 24 |
| F114001729 | T61 HousingLH | 料箱 | 合格 | 648 | 1352 | 144 |
| F114001730 | T61 HousingRH | 料箱 | 合格 | 264 | 1368 | 0 |
| F114001731 | T61 LensLH | 料箱 | 合格 | 228 | 954 | 0 |
| F114001732 | T61 LensRH | 料箱 | 合格 | 180 | 1480 | 0 |
| F114001733 | T63 HousingLH | 料箱 | 合格 | 520 | 1048 | 0 |
| F114001734 | T63 HousingRH | 料箱 | 合格 | 444 | 1138 | 0 |
（4）库存计算公式
合计入库：=SUMPRODUCT((入库!$C$2:$C$65535=库存!$A2)*(入库!$E$2:$E$65535=库存!$C2)*(入库!$F$2:$F$65535=库存!$D2)*(入库!$D$2:$D$65535))
合计出库：=SUMPRODUCT((出库!$C$2:$C$65535=库存!$A2)*(出库!$E$2:$E$65535=库存!$C2)*(出库!$F$2:$F$65535=库存!$D2)*(出库!$D$2:$D$65535))
公式中每一组数字的条件判定。就会得出不同的“真”与“假”，数组的值变成了不同的“0”和“1”，各组的数字相乘，仅仅有条件全然达到的行。才有可能得到数值。这些数值相加的结果就是我们要求的值。这就是SUMPRODUCT方式的多条件求各和。上述第一个公式能够描写叙述为（第二个公式同理）：
=SUMPRODUCT((入库零件号区域=库存零件号)*(入库包装种类区域=库存包装种类)*(入库质量状态区域=库存质量状态)*(入库数量区域))
库存数量：=E2-F2
2、每日质量通报
Excel文件里有三张表，一张质量记录汇总表，一张每日通报，一张累计通报，在加入每天质量记录后（点击button。VBA程序自己主动依据P1单元格中的日期从数据库中提取质量记录）。每日通报和累计通报用sumproduct函数自己主动反应当前质量情况。改动日期，每日通报和累计通报两张报表马上反应当日的质量数据。
（1）质量记录A、B、C、D、E、F列
| 投递日期 | 投递单位 | 时限情况 | |||
| 进口邮件数 | 未及时妥投邮件数 | 未妥投邮件数 | 及时妥投率 | ||
| 2014-9-1 | 合肥市 | 168 | 30 | 7 | 77.98% |
| 2014-9-1 | 阜阳市 | 44 | 3 | 0 | 93.18% |
| 2014-9-1 | 蚌埠市 | 40 | 1 | 0 | 97.50% |
| 2014-9-1 | 芜湖市 | 103 | 7 | 0 | 93.20% |
| 2014-9-1 | 安庆市 | 60 | 9 | 0 | 85.00% |
| 2014-9-1 | 宿州市 | 45 | 4 | 0 | 91.11% |
| 2014-9-1 | 滁州市 | 73 | 1 | 0 | 98.63% |
| 2014-9-1 | 六安市 | 52 | 1 | 0 | 98.08% |
| 2014-9-1 | 黄山市 | 17 | 3 | 0 | 82.35% |
| 2014-9-1 | 淮北市 | 15 | 1 | 0 | 93.33% |
| 2014-9-1 | 亳州市 | 34 | 2 | 1 | 91.18% |
| 2014-9-1 | 淮南市 | 37 | 1 | 1 | 94.59% |
| 2014-9-1 | 马鞍山 | 24 | 1 | 0 | 95.83% |
| 2014-9-1 | 宣城市 | 29 | 3 | 0 | 89.66% |
| 2014-9-1 | 铜陵市 | 23 | 4 | 0 | 82.61% |
| 2014-9-1 | 池州市 | 21 | 3 | 0 | 85.71% |
| 2014-9-2 | 合肥市 | 270 | 109 | 8 | 56.67% |
| 2014-9-2 | 阜阳市 | 62 | 7 | 0 | 88.71% |
| 2014-9-2 | 蚌埠市 | 51 | 1 | 0 | 98.04% |
| 2014-9-2 | 芜湖市 | 107 | 10 | 0 | 90.65% |
| 2014-9-2 | 安庆市 | 72 | 5 | 0 | 93.06% |
（2）每日通报A、B、C、D、E、F列
| 投递单位 | 时限情况 | |||
| 进口邮件数 | 未及时妥投邮件数 | 未妥投邮件数 | 及时妥投率 | |
| 合肥市 | 168 | 30 | 7 | 77.98% |
| 阜阳市 | 44 | 3 | 0 | 93.18% |
| 蚌埠市 | 40 | 1 | 0 | 97.50% |
| 芜湖市 | 103 | 7 | 0 | 93.20% |
| 安庆市 | 60 | 9 | 0 | 85.00% |
| 宿州市 | 45 | 4 | 0 | 91.11% |
| 滁州市 | 73 | 1 | 0 | 98.63% |
| 六安市 | 52 | 1 | 0 | 98.08% |
| 黄山市 | 17 | 3 | 0 | 82.35% |
| 淮北市 | 15 | 1 | 0 | 93.33% |
| 亳州市 | 34 | 2 | 1 | 91.18% |
| 淮南市 | 37 | 1 | 1 | 94.59% |
| 马鞍山 | 24 | 1 | 0 | 95.83% |
| 宣城市 | 29 | 3 | 0 | 89.66% |
| 铜陵市 | 23 | 4 | 0 | 82.61% |
| 池州市 | 21 | 3 | 0 | 85.71% |
| 累 计 | 785 | 74 | 9 | 89.43% |
计算公式（单元格P1存放通报日期）：
进口邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!C$4:C$500))
未及时妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!D$4:D$500))
未妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!E$4:E$500))
及时妥投率 ：=(B4-C4-D4)/B4
（3）累计通报
内容和每日通报一样仅仅是数据为当月累计而已，所以各字段公式也几乎相同，仅仅是多了个小于符号“<”。即：
进口邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500<=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!C$4:C$500))
未及时妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500<=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!D$4:D$500))
未妥投邮件数：=SUMPRODUCT((数据汇总!$A$4:$A$500<=$P$1)*(数据汇总!$B$4:$B$500=$A4)*(数据汇总!E$4:E$500))
及时妥投率 ：=(B4-C4-D4)/B4
