菜鸟学excel：SUMPRODUCT与IF组合的条件求和函数的公式优化问题

Question

D3-F8是原始数据
A3-A7是1班开学报名时的学生姓名，现在需要按期初的学生姓名把F3-F8的语文成绩一一对应到B3-B8中,缺考的记为-2

在B3输入公式拉到B8即可实现
公式为=IF(((SUMPRODUCT((($D$3:$D$9=$A3)*($E$3:$E$9=$A$1))))=0)*(A3<>""),-2,SUMPRODUCT(($D$3:$D$9=$A3)*($E$3:$E$9=$A$1)*(F$3:F$9)))
意思：A3的吴存在于D3—D8中的（D3）单元格，E3发现吴是1班的，所以把F3单元格的成绩显示到B3单元格中
A4的洪存在于D3-D8中的（D4）单元格，E4发现D4单元格的洪是2班的不是1班的（即同名不同班，不是一个人），意味着1班的洪缺考，在B4单元记为-2
A5的陈不存在于D3-D8中，即1班的陈没有成绩记录，在B5单元格记为-2，表示缺考。
公式求值时出现错误项
因我的是excel2003，所以只测试了9999djx给的公式，公式求值时提示“excel遇到问题需要关闭，我们对此引起的不便表示抱歉”，我发现我这只要是IF(INDEX())都会这样，我在之前已发出求助请求http://zhidao.baidu.com/question/1820081921320112468.html
akazao 、James0yuxuan、9999djx你们的公式都漏了一个条件，A列不是空值不是0值才记-2，若为空值或0值记为0，后面需要统计人数等
如：A8是空值，所以B8记0，而9999djx测试结果是记-2
看看我公式的条件(SUMPRODUCT((($D$3:$D$9=$A3)*($E$3:$E$9=$A$1))))=0)*(A3<>"")，我只是觉得这样的表达很别扭，不知道能否有更简洁的表达方式？而且公式求值有错误值#N/A？

Answer 1

2007 版本以上：
=if(countifs(D:D,A3,E:E,$A$1),sumifs(F:F,D:D,A3,E:E,$A$1),-2)

=======================
漏条件 是故意漏的，因为不需要这个条件 不管是 空还是 0， 求和都是0

你说的简单的表达方式， 2007 可以用Iferror, 2003 比较长，之前没准备写

=iferror(lookup(1,0/((D$1:D$100=A3)*(E$1:$E100=$A$1)),F$1:F$100),-2)

Answer 2

B3里的公式如下，shift+ctrl+回车，下拉
=IF(ISERROR(INDEX(F$3:F$8,MATCH(A3&$A$1,D$3:D$8&E$3:E$8,0))),-2, INDEX(F$3:F$8,MATCH(A3&$A$1,D$3:D$8&E$3:E$8,0)))

Answer 3

如果你用的是07版以上的excel，可以用这个公式，更简单些
=IF(COUNTIFS(D:D,A3,E:E,$A$1)=0,-2,SUMIFS(F:F,D:D,A3,E:E,,$A$1))追问

刚关了电脑，明天试试，我的是2003版本

追答

上面公式多了个符号，以这个为准=IF(COUNTIFS(D:D,A3,E:E,$A$1)=0,-2,SUMIFS(F:F,D:D,A3,E:E,$A$1))

本回答被网友采纳